MYSQL wildcard query help

[Gossie]

Hi
Getting stuck on this wildcard query.
I've got a connection to the MYSQL database and a static query works; but this wildcard query won't - just returns a zero array:
***Query in MS Access to a linked table that works:***
SELECT postop.id, postop.case_number, postop.hospital_number, postop.recorded, postop.call_date
FROM postop
WHERE (((postop.call_date) Like "20/12/2012*"));
***
My Macro Scheduler code:
Let>SQLcrit=20/12/2012%
Let>SQL2=SELECT postop.id, postop.case_number, postop.hospital_number, postop.recorded, postop.call_date FROM postop WHERE (((postop.call_date)LIKE'%SQLcrit%'));
****If i do this query - it works and returns data:

Let>SQL3=SELECT postop.id, postop.case_number, postop.hospital_number FROM postop WHERE (((postop.hospital_number)="F12345"))

DBQuery>dbH,SQL2,contacts,nr,nf,1
Wait>2
//Connects to database and says dbH=1
DBClose>constr

[Gossie]

Solving my own question here; but thought I would post as it might help someone else down the line.....

It’s not the wildcard on the LIKE. It’s the format of the date. Found out that MS-Access will be translating it through the ODBC layer based on locale.

MySQL will expect it American native ie. 2012-12-20. Doesn’t have to be that way, but we never bothered configuring the locale on MySQL, just worked around it - so using MacroScheduler to switch the date round first; before passing it to the query.

[Marcus Tettmar]

Hello, and Happy New Year!

You can also use the MYSQL date functions in the SQL to convert to/from specified formats:

http://dev.mysql.com/doc/refman/5.5/en/ ... tions.html