How to convert sql query for MS Access in ODBC

Question

Here is table images which is given below

enter image description here

My Query is give below,

$DB2->query("SELECT CHECKINOUT.USERID,USERINFO.CardNo FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID WHERE CHECKTIME BETWEEN '$start_date' AND '$tommorow' AND USERINFO.CardNo <> NULL")

I want to do query in ODBC Like below

$DB2->query("SELECT CHECKINOUT.USERID,USERINFO.CardNo FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID WHERE format(CHECKTIME, 'dd/mm/yyyy') BETWEEN '$start_date' AND '$tommorow' AND USERINFO.CardNo <> NULL")

But The Query for ODBC it is not work perfectly, it has come all date data with date difference data.

How to make perfect query in ODBC FOR MS ACCESS Database.

Please give me any solution.

The Query will work to take date , I will input only date(Not datetime). For these reason i used date format. Please any suggestion. — MD. ABDUL Halim
Format turns a date into a string. If you were going to use that and you shouldn't, all three dates need to be in the same format and it has to be in Year Month Day (or Year Day Month) with leading zeros or it will never work. Always remember '04/05/2013' is not a date it's a string. Take the format function out of your query and see what happens, I can only guess from here, based on the info you have given. — Tony Hopkinson

pghcpa pghcpa · Accepted Answer · 2014-05-06T23:24:36

    SELECT CHECKINOUT.USERID,USERINFO.CardNo FROM CHECKINOUT
    INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID
    WHERE CHECKTIME
    BETWEEN #$start_date# AND #$tommorow# AND USERINFO.CardNo <> NULL

I see from your screen images that CHECKTIME is a datetime data type. If you are sending the query to MS-Access, the dates must be surrounded by pounds signs (#) and not single quotes. You don't need to format them as string, the default for using a date will be midnight (00:00) unless you specify otherwise.

The best solution is to paste this query into the MS-Access query designer and make sure it gives you the results you expect. Failing that, create the query in the MS-Access designer and look at the SQL it returns for format.

Note if you say between startdate and tomorrow that would between, for example 6-May-2014 at 00:00 through 7-May-2014 at 00:00. So it depends what you mean by "tomorrow" (beginning of the day or end of the day).

How to convert sql query for MS Access in ODBC

1 Answers