 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / June 2007Date Values and Pass Through Queries
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 > I am trying to get a Date Value in the format YYYY-MM from a MySQL [quoted text clipped - 4 lines] > 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 > 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 [quoted text clipped - 8 lines] > > understand it? > > Any help would be greatly appreciated >>> Hi >>> I am trying to get a Date Value in the format YYYY-MM from a MySQL [quoted text clipped - 4 lines] >>> 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.  SignatureRick 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. > >>> Hi > >>> I am trying to get a Date Value in the format YYYY-MM from a MySQL [quoted text clipped - 9 lines] > 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. 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. SignatureRick 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 > > 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. > 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? SignatureRick 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 ???? > > Yep 100% Sure its a pass through query > > [quoted text clipped - 3 lines] > 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 ???? 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. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ok, well thanks for you help anyway > > To get a date vale in YYYY-MM format where the date value is called > > "request_dat" you use the following funtion [quoted text clipped - 5 lines] > 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. 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 > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.