Delete Query not Working

S

SSi308

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.
 
J

John Spencer

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
 
S

SSi308

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 said:
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
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.
Click to expand...
.
Click to expand...
 
J

John Spencer

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
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 said:
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
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.
Click to expand...
.
Click to expand...
Click to expand...
 
S

SSi308

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 said:
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
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 said:
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.
.
Click to expand...
Click to expand...
.
Click to expand...
 
J

John Spencer

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 said:
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
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 said:
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.
.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Totals Query 24
Reference controls on a dialogue form as the parameters 2
Delete Query Help 2
Delete Queries from an Unmatched query 6
Delete records 5
Optionbutton Not working 3
Weekly Report 2
Save workbook without shapes 0

Top