Data Type Mismatch 1

[BiJae]

I have a MySql database that is being passed through an Access Database front end using linked tables. I have several queries that are built on various date fields. I recently upgraded the MySql database to v 5.0. I can connect, import new tables, browse data, run queries etc. However each time I try to run a query on a date field I get a "Data type mismatch" error. This is one of the simple queries I'm running:

SELECT DISTINCTROW (select name from creditunions where creditunions.cuindex = build.cuindex) AS CreditUnion, bylaw.Description, bylaw.FinalActionDate
FROM bylaw INNER JOIN BUILD ON bylaw.CUIndex = BUILD.CUIndex
WHERE (((bylaw.FinalActionDate)="11/29/2005"));

I verified that the field type in the Access side of the table is Date/Time. Also the MySql side of the database shows that the field is a Date field. I can remove the where clause and run the query without error.

I copied the date 11/29/2005 from the query ran without the where clause. when I put that piece in using the gui receive the data type mismatch error again. Can any one tell me if they've seen this happen before? Is there a way around this?

Any advice is appreciated.

Thank you

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[lamarw]

Don't forget to use the Date/Time delimiters. It tells Access to convert the date time to internal format instead of using it as a string:

WHERE (((bylaw.FinalActionDate)=#11/29/2005#));

 

[BiJae]

thank you!

I've been away from Access too long and forgot that little nuance..

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart