Prev: Last record/entry from a table
Next: 2007 Concatenation problems
From: John Spencer on 7 Jun 2010 09:24
You can write that as
DELETE [NumberDialed]
FROM [DailyCalls]
WHERE [DailyCalls].[NumberDialed] IN
(SELECT [PersonalCalls].[PhoneNumber]
FROM [PersonalCalls]
WHERE [PersonalCalls].[PhoneNumber] is not null)

Although in my experience I have not needed to reference a field (or all
fields) in a delete query. Then again, if you are working in the query design
view and not in the SQL view (guess where I most often work), it may be a
requirement.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
> My error. Exteraneous AND
>
> DELETE
> FROM [DailyCalls]
> WHERE [DailyCalls].[NumberDialed] IN
> (SELECT [PersonalCalls].[PhoneNumber]
> FROM [PersonalCalls]
> WHERE [PersonalCalls].[PhoneNumber] is not null)
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> SSi308 wrote:
>> John,
>>
>> I tried this syntax but get an error:
>> DELETE
>> FROM DailyCalls
>> WHERE NumberDialed in ( SELECT PhoneNumber FROM [PersonalCalls]
>> AND PhoneNumber is Not Null);
>>
>> Error is: Syntax error in query expression 'NumberDialed in (SELECT
>> PhoneNumber FROM [PersonalCalls] AND PhoneNumber is Not Null)'.
>>
>> Thanks, Lori
>>
>> "John Spencer" wrote:
>>
>>> It should be
>>>
>>> DELETE DistinctRow [DailyCalls].NumberDialed
>>> FROM DailyCalls
>>> WHERE (((DailyCalls.NumberDialed)=[PersonalCalls].[PhoneNumber]));
>>>
>>> However that will fail since you do not a have a reference to
>>> PersonalCalls in the query.
>>>
>>> You MIGHT be able to use
>>> DELETE DistinctRow [DailyCalls].NumberDialed
>>> FROM DailyCalls INNER JOIN [Personal Calls]
>>> ON DailyCalls.NumberDialed=[PersonalCalls].[PhoneNumber]
>>>
>>> The following should work with no problem
>>> DELETE
>>> FROM DailyCalls
>>> WHERE NumberDialed in
>>> ( SELECT PhoneNumber
>>> FROM [Personal Calls]
>>> AND PhoneNumber is Not Null)
>>>
>>> John Spencer
>>> Access MVP 2002-2005, 2007-2010
>>> The Hilltop Institute
>>> University of Maryland Baltimore County
>>>
>>> SSi308 wrote:
>>>> I have tried to follow many of the suggestions in other posts, but
>>>> have not been able to get this to work.
>>>>
>>>> I have a table called DailyCalls and another table called
>>>> PersonalCalls. A query was set up to return calls in the DailyCalls
>>>> table that match calls in the PersonalCalls table. When I change the
>>>> query to a Delete query I get the error: Invalid bracketing of name
>>>> '[PersonalCalls.PhoneNumber]'.
>>>> Here is the sql of the delete query:
>>>> DELETE DistinctRow [DailyCalls].NumberDialed
>>>> FROM DailyCalls
>>>> WHERE (((DailyCalls.NumberDialed)=[PersonalCalls.PhoneNumber]));
>>>>
>>>> I tried removing brackets and also adding sqare brackets around the
>>>> table names, but get the same error.
>>>>
>>>> Can anyone tell me what I have wrong?
>>>>
>>>> Thanks.
>>> .
>>>
First  |  Prev  | 
Pages: 1 2
Prev: Last record/entry from a table
Next: 2007 Concatenation problems