Deleting Data depending upon dates

[k209310]

I ma having trouble with the DB execute statement.

i am trying to delete data from a table that is over thirty days old. Curerntly i am using the code:

Dim DeleteDate As Date
DeleteDate = Format(Date - 30, "DD/MM/YYYY")

CurrentDb.Execute "DELETE * FROM tblCustomerMeeting WHERE MeetingDate<" & DeleteDate & ";", dbFailOnError

The table field is set to a short date. statement seems only to delete some of the data but not all for example if i have date of #04/03/2003# in my table it will; not be deleted

Am i going about deleteing data from a table in the right way? Is there a better way i could be doing this

Thanks

Chris

 

[Rich]

DeleteDate = DateAdd("d",-30,Date())

 

[k209310]

Thanks for the reply Rich

My problem seems to be in the WHERE Statement of the sql the date function you provided calculates the correct date but it doesnt to delete the records. Ie it doesnt delete the records that are older than the current date. (WHERE MeetingDate<" & DeleteDate & ";")

Has anyone got any idea how i can do this

Cheers

Chris

 

[RichardJ]

first up you should try and get used to using "dd mmm yyyy" for all your date formats, stops potential confusion with US / UK date formats.

Then I would put "#" at the start and end of the dates this tells Access that this the following value is a date.

so I'd have something like this...


DeleteDate = Format(Date - 30, "DD MM YYYY")

CurrentDb.Execute "DELETE * FROM tblCustomerMeeting WHERE MeetingDate < #" & DeleteDate & "#", dbFailOnError


Hope that helps you...