By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,449 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Delete records by AutoNumber

P: 5
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?
Mar 1 '07 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What's the SQL?
Mar 1 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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...

Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM TableName
  2. WHERE FieldName Is Null;
  3.  
Mary
Mar 1 '07 #3

P: 5
What's the SQL?
DELETE tblAccounts.*, tblAccounts.Reference
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));
Mar 1 '07 #4

P: 5
Try this...

Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM TableName
  2. WHERE FieldName Is Null;
  3.  
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]));
Mar 1 '07 #5

Rabbit
Expert Mod 10K+
P: 12,366
DELETE tblAccounts.*, tblAccounts.Reference
FROM tblAccounts
WHERE (((tblAccounts.Reference)=[Enter Reference Number]));
Try:
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM tblAccounts
  3. 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.
Mar 1 '07 #6

P: 5
Try:
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM tblAccounts
  3. 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.
Mar 1 '07 #7

P: 5
I'll try it.
Sorry. No Cigar.
Mar 1 '07 #8

Rabbit
Expert Mod 10K+
P: 12,366
If Reference is a Text field:
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM tblAccounts
  3. WHERE (((tblAccounts.Reference)= "'" & [Enter Reference Number] & "'"));
If Reference is a Numeric field:
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM tblAccounts
  3. WHERE (((tblAccounts.Reference)= Val([Enter Reference Number])));
Mar 1 '07 #9

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM tblAccounts
  3. 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.
Mar 5 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.