Delete Query - using date as criteria VBA

[tuktuk]

this sounds wierd but is there a way to use vba to delete records from a tbl that have a date assigned to the record where the date does not exist.....for example: #2/30/10#, #2/31/10#, #4/31/11#, etc?

i iknow this is wierd but the table was build using queries that did not limit this dates that do not exists and therefore made a record for them.

thanks
t

 

[xenou]

I am not finding it is possible to enter such dates in Access (trying to do so just now).
Is this a text field?
How many records are in the table?

 

[tuktuk]

no, it is a date field......one table in the database (total mdb size 1.2G)

THANKS

 

[james_lankford]

i iknow this is wierd but the table was build using queries that did not limit this dates that do not exists and therefore made a record for them.

i don't think that makes sense
Queries don't have to limit dates to be valid dates.
The fact that they are dates limits them to dates. If the field in the table is designed as a date, then it has to be a valid date to go inside.
I even tried making the field text, entering 2/30/2011 and then converting the field to date and access just deleted the data.

 

[xenou]

I'm at a loss - not able to recreate the problem (how did you get the data in the table?)

One try at cleanup may be to create a table of all valid dates, then Left Join these to the table to it and delete all null (non-matching) rows. You can generate such a list in seconds in Excel.

Or a write a custom MyFieldIsADate() function.

Or even try Access's built in IsDate() function and see if it returns true or false.

ξ

 

[revans]

If the field is a date/time field Access won't let you enter a bad date. I't probably a text field. Nevertheless, you *could* create a new text field, copy the "dates" into this new field. Then run your query based on these bad dates in the new field. When you're done cleaning up you would then delete this new field.

hth,

Rich