If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Delete Query not Working
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. |
#2
|
|||
|
|||
Delete Query not Working
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. |
#3
|
|||
|
|||
Delete Query not Working
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. . |
#4
|
|||
|
|||
Delete Query not Working
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. . |
#5
|
|||
|
|||
Delete Query not Working
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. . . |
#6
|
|||
|
|||
Delete Query not Working
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. . |
Thread Tools | |
Display Modes | |
|
|