SQL DateValue() : Datatype mismatch ... HELP!

[maxx]

Hi alltogether,

I knew, Access an VB had some Problems with empty DateFields, now I'm sure.

I have got a table which contains some Date Fields, which can, but need not to be filled. Each month the ones with a Date older than TodaysDate will must be deleted.

If I do the following SQL statement to select the records, I get a Datatype mismatch error.

"SELECT * FROM tblTable WHERE DateValue(DateField) <= Date()"

This Statement needs the DateValue() Function because otherwise I get Dates like 05.07.00 (with dd/mm/yy) when TodaysDate is 10.06.00. (because 5 is bigger than 10)

Access is strange ... perhaps anyone can help me in my desaster?

PS: The Select-Statement works! I tested it. Only if some Fields are Empty this Error occurs.

[Ianpbaker]

Hi maxx

Why don't you check to see whether the column Is null first

"SELECT * FROM tblTable WHERE DateField IS NOT NULL AND DateValue(DateField) <= Date()"

That should sort out your problem

Ian

[maxx]

Nope, it does not ... it only does, if I create a query first "SELECT * FROM tblTable WHERE ... IS NOT NULL"

But that's not what I want, because I would have to create tons of Querys just to delete them afterwards ... this would screw up my Database, because it's Access ...

Thanks anyway, any further suggestions?

maxx