Reading Date from SQL for query criteria

[gangel]

Hey pplz,
So I have an access frontend that I have made, worked 100% and all was well...

THEN... the company moved to sql.

I have reattached to the sql backend and everything is great EXCEPT the dates in sql are stored as text for some reason.


"2010-11-06 00:00:00"

BUT it is a text format....



so my criteria has <Date() and it returns EVERYTHING :s

Ideas?

Changing the real table format to date isn't really an option as it is a "proper" program and I don't want to go around screwing with stuff like that randomly.

Regards,

Glenn

[June7]

Try using CDate() in a query to convert to date value and apply filter to that constructed field.

[gangel]

yeh issue is that it is an aggregate and there are nulls.

Currently Using
DateDispensed (the sql version) in one column
MyDate: Cdate(datedispensed)



then doing a query that looks at that query (subquery?) which uses the newly constructed Cdates.

If I aggregate the initial query it has errors

[June7]

Maybe provide an alternate value when null that would not be captured by the filter:

CDate(Nz([fieldname], "12/31/2199"))

Might use Is Null in query instead of Nz. Review http://allenbrowne.com/QueryPerfIssue.html