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

Deleting records with a date that passed.

P: 19
Hi there,
since this is my very first post I hope I did it right.. Read the guidelines, so I doubt I do to much wrong.

I am working in Acces 2000 and I have to make a database for a "Rent A Car" company.

The company wants me to make it able to see what cars are rented and which are not, and so make sure that a car won't be rented twice. There is a record for a start date and with an end date. When the end date has passed it should deleted the record, since the car is back at the company. Now problem is that I have no clue how to do this.

I'd like some help with this please.

If there is any more info you need, just say it and I'll give it.
Nov 30 '06 #1
Share this Question
Share on Google+
4 Replies


Expert 5K+
P: 8,434
At the simplest level, an example of SQL to delete a record where the date is passed might go something like...
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM Table1 WHERE DateField < Date();
Date( ) is the system function which returns the current date.
Nov 30 '06 #2

P: 19
Okay, I know you may find me an idiot, but for some reason I can't get this to work.

Would you mind explaining it a bit more detailed?
Thanks
Nov 30 '06 #3

Expert 5K+
P: 8,434
Okay, I know you may find me an idiot, but for some reason I can't get this to work. Would you mind explaining it a bit more detailed?
First, a question. The field which holds your date; is the field type Date/time? Assuming it is...

In Access 2003, try this.
  • Create a new query
  • Choose the table that you want to delete the records from
  • Pull down the Query menu and select Delete Query
  • Grab your end-date field from the field list, and drag it onto the grid in the bottom half of the design window
  • In the "Criteria" entry for that field, enter < Date()
You should now have a query which will delete from your table all records whose end date is before today. You have a number of options:
  • Run it from where you are
  • Save it, and later run it by name, from a macro or VBA code if desired
  • Switch to SQL view and copy the SQL syntax which Access has generated for you. You can then use this SQL string in a macro or VBA code, or wherever.
Nov 30 '06 #4

P: 19
Thanks a lot for your help. Finally got it to work.
Dec 4 '06 #5

Post your reply

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