Date Values and Pass Through Queries

[Guest]

Hi
I am trying to get a Date Value in the format YYYY-MM from a MySQL database
using a pass through query. When I run the query I get this result - 〲㘰〭.
If I run this query in MySQL i get the correct result - Why does Access not
understand it?
Any help would be greatly appreciated

 

[Pat Hartman \(MVP\)]

Since users shouldn't be looking at queries in any event, it would be better
to set the format property of the form or report control to format the date
field.

 

[Guest]

Hi Pat
Thanks for your input, however I only really use Access for the Pass through
query functionality (Its the quickest way to get data from a MySQL database
into Excel). I therefore dont have any forms or reports, i just use the
query interface

 

[Rick Brandt]

By definition, a passthrough query uses the "flavor" of SQL that is
supporeted by the server you are connecting to (in your case MySQL). There
should be nothing for "Access" to understand, only MySQL needs to understand
it.

 

[Guest]

When the same query is run in non Access Applications, such as MySQL Query
Browser, it works fine - so the query works ok.

 

[Rick Brandt]

When the same query is run in non Access Applications, such as MySQL
Query Browser, it works fine - so the query works ok.

And your quite sure it is a passthrough query and not a local query against
a linked table? If so, that would make me wonder about the driver you are
using more than anything else.

 

[Guest]

Yep 100% Sure its a pass through query

To access the data I am using ODBC MySQL 3.5.1 Driver if that helps

 

[Rick Brandt]

Yep 100% Sure its a pass through query

To access the data I am using ODBC MySQL 3.5.1 Driver if that helps

And you are getting ???? in the datasheet of the passsthrough query wher you
shoudl get the date? What syntax does MySQL use to return a date in a
specified format?

 

[Guest]

To get a date vale in YYYY-MM format where the date value is called
"request_dat" you use the following funtion

date_format(request_date_sold,'%Y-%m')

Access returns ????

 

[Rick Brandt]

To get a date vale in YYYY-MM format where the date value is called
"request_dat" you use the following funtion

date_format(request_date_sold,'%Y-%m')

Access returns ????

Mystery to me. Any time I have used a passthrough query that returned a
type Access didn't understand it just treated it as text.

 

[Guest]

ok, well thanks for you help anyway

Mystery to me. Any time I have used a passthrough query that returned a
type Access didn't understand it just treated it as text.

 

[Guest]

I have found a solution

There was a mismatch with the data types

So I have done this

cast(date_format(date,'%Y-%m') as char)

This seems to work