Delete Query not Working
in [Queries]
|
From: SSi308 on 2 Jun 2010 13:15 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.
From: John Spencer on 2 Jun 2010 13:24 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.
From: SSi308 on 4 Jun 2010 10:46 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. > . >
From: John Spencer on 6 Jun 2010 15:58 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. >> . >>
From: SSi308 on 7 Jun 2010 08:07
John, Now I get the error: Query must have at least one destination field. Do I need to add a field reference to DELETE? Thanks for the help. Lori "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. > >> . > >> > . > |