Date Query

[jimleon]

I have a query that selects all records between 2 dates. The follow up date field is stored in text format. I keep receiving a"This expression is typed incorrectly, or it is too complex to be evaluated"

The offending line is shown thus.

WHERE ((CDate([follow_up_date])) Between [Start Date] And [End Date]))

Now the weird thing is it worked last week.... I am confused...

[Avellano]

I have a query that selects all records between 2 dates. The follow up date field is stored in text format. I keep receiving a"This expression is typed incorrectly, or it is too complex to be evaluated"

The offending line is shown thus.

WHERE ((CDate([follow_up_date])) Between [Start Date] And [End Date]))

Now the weird thing is it worked last week.... I am confused...

I can only assume you have some garbage in one or more records in the follow_up_date field.

Why not change that field to date format and apply an input mask

Regards

Avellano

[jimleon]

Yes but the follow up date field is delibrately in text format for a good reason. I run another query on the same field where i compare it to todays date and that works so i'm sure the data is ok. And as I said it did work last week!!!

I can only assume you have some garbage in one or more records in the follow_up_date field.

Why not change that field to date format and apply an input mask

Regards

Avellano

[MMcCarthy]

There can be issues with date formats. Impose a format (American or European, whichever you use.

[CODE=sql]
WHERE (CDate(Format([follow_up_date], "mm/dd/yyyy")) Between [Start Date] And [End Date]))[/CODE]

See if that works, if not try format after CDate and see if that works.