DateValue & Type Mismatch Error

[Guest]

I have a field [dte1] with date-like text. I created another field ....
EventDate: DateValue([dte1]). It works great. I made the Event Date a long
date and I get a long date; I make it a short date, and I get a short date.

HOWEVER, when I try to put >date() in the criteria, I get a Type Mismatch
Error. I also get a mismatch error when I put in >1 and >"today". This worked
in a previous database with no problems.

Here is my sql statement without the criteria that runs with no problems:

SELECT Events.EID, Events.order, Events.code, Events.category1,
DateValue([dte1]) AS EventDate, Events.dte1, Events.title1, Events.details1,
Events.details21, Events.details31, Events.details41, Events.details51,
Events.email, Events.phone
FROM Events
WHERE (((Events.code)="5263") AND ((Events.category1)="convention"))
ORDER BY Events.order, Events.dte1;

Here is my SQL statement with it that results in Data type mismatch in
criteria expression:

SELECT Events.EID, Events.order, Events.code, Events.category1,
DateValue([dte1]) AS EventDate, Events.dte1, Events.title1, Events.details1,
Events.details21, Events.details31, Events.details41, Events.details51,
Events.email, Events.phone
FROM Events
WHERE (((Events.code)="5263") AND ((Events.category1)="convention") AND
((DateValue([dte1]))>Date()))
ORDER BY Events.order, Events.dte1;


Any ideas? Thanks!

 

[Allen Browne]

Is there a need to use DateValue()?

Assuming dte1 is a Date/Time field, and that the problem is that it contains
a date as well as a time, could you use:
[dte1] >= (Date() + 1)

At worst, you could correct the problem by typecasting with CDate(), e.g.:
CDate(DateValue([dte1])) > Date()

 

[Guest]

Actually, I found the answer to this right after I posted the question ...
there was a blank record in the table. I deleted the blank record and it
worked great after that. Thanks!