-
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.
-
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
-
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
