Delete records by AutoNumber

[Mike46]

I am trying to delete some blank records (all they have is a date and the autonumber field) in a table. A select query runs fine when selecting the offending record but when I change it to a delete query I get "The search key was not found in any record" Am I standing on the right foot or what?

[Rabbit]

What's the SQL?

[MMcCarthy]

I am trying to delete some blank records (all they have is a date and the autonumber field) in a table. A select query runs fine when selecting the offending record but when I change it to a delete query I get "The search key was not found in any record" Am I standing on the right foot or what?

Try this...

DELETE * FROM TableName
WHERE FieldName Is Null;
 

Mary

[Mike46]

What's the SQL?

DELETE tblAccounts.*, tblAccounts.Reference
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));

[Mike46]

Try this...

DELETE * FROM TableName
WHERE FieldName Is Null;
 

Mary

Didn't work! The field name is not Null. The SQL is
DELETE tblAccounts.*, tblAccounts.Reference
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));

[Rabbit]

DELETE tblAccounts.*, tblAccounts.Reference
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));

Try:

DELETE *
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));

But note that this code does not do what you originally wanted in your post. It only deletes the records with the reference number that is specified by the user.

[Mike46]

Try:

DELETE *
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));

But note that this code does not do what you originally wanted in your post. It only deletes the records with the reference number that is specified by the user.

I'll try it.

[Mike46]

I'll try it.

Sorry. No Cigar.

[Rabbit]

If Reference is a Text field:

DELETE *
FROM tblAccounts
WHERE (((tblAccounts.Reference)= "'" & [Enter Reference Number] & "'"));

If Reference is a Numeric field:

DELETE *
FROM tblAccounts
WHERE (((tblAccounts.Reference)= Val([Enter Reference Number])));

[NeoPa]

I am trying to delete some blank records (all they have is a date and the autonumber field) in a table. A select query runs fine when selecting the offending record but when I change it to a delete query I get "The search key was not found in any record" Am I standing on the right foot or what?

We need some proper information.
Please provide the SQL of the query you say worked.
What do you mean when you say 'Blank Records'?
We have the first part OK :

DELETE
FROM tblAccounts
WHERE ...

It's the WHERE clause which is holding us up, and for that we need the information which you haven't provided yet.
Records can look 'Blank' under a number of different circumstances. That's why we need the SQL of the query that did work to know how to determine 'Blank' in your circumstances. I've known a number of quite experienced users get confused over Nulls and Empty Strings etc.

Rabbit, A parameter isn't treated as a literal in SQL so won't need delimiters. Good thinking though.