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.

"ExcelEd" <(E-Mail Removed)> wrote in message
news:6A13F758-7787-4A35-B730-(E-Mail Removed)...
> 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

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

"Pat Hartman (MVP)" wrote:

> 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.
>
> "ExcelEd" <(E-Mail Removed)> wrote in message
> news:6A13F758-7787-4A35-B730-(E-Mail Removed)...
> > 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
>
>
>

ExcelEd wrote:
>>> 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

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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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" wrote:

> ExcelEd wrote:
> >>> 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
>
> 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>
>
>

ExcelEd wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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" wrote:

> ExcelEd wrote:
> > 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.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

ExcelEd wrote:
> 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?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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" wrote:

> ExcelEd wrote:
> > 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?
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

ExcelEd wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com