Date Values and Pass Through Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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.
 
When the same query is run in non Access Applications, such as MySQL Query
Browser, it works fine - so the query works ok.
 
ExcelEd said:
When the same query is run in non Access Applications, such as MySQL
Query Browser, it works fine - so the query works ok.
Click to expand...

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.
 
Yep 100% Sure its a pass through query

To access the data I am using ODBC MySQL 3.5.1 Driver if that helps
 
ExcelEd said:
Yep 100% Sure its a pass through query

To access the data I am using ODBC MySQL 3.5.1 Driver if that helps
Click to expand...

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?
 
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 ????
 
ExcelEd said:
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 ????
Click to expand...

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.
 
ok, well thanks for you help anyway

Rick Brandt said:
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.
Click to expand...
 
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
 
Back
Top