why my query "CONVERT" in Microsoft Access odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'CONVERT' in expression.

This is my query

<?php

// setup database for your microsoft Access
// you can setup password in you microsoft Access

// this is a variable for your connection in odbc
// "zkConnection" is your ODBC Data Source Administrator
$conn = odbc_connect("zkConnection", "", "");

// create condition for testing conecction
if ($conn) {
    // echo "<br>Connection Established</br>";
} else {
    echo "Koneksi Gagal";
}

$from_date = $_REQUEST['from_date'];
$to_date = $_REQUEST['to_date'];

$query = "INSERT INTO CalculateData(USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, datein, timein, dateout, timeout)
          SELECT USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, CONVERT(date, CHECKTIME) AS datein, MIN(CHECKTIME) AS timein, CONVERT(date, CHECKTIME) AS dateout, MAX(CHECKTIME) AS timeout
          FROM TransactionLog
          WHERE CHECKTIME BETWEEN #$from_date# AND #$to_date#
          GROUP BY USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, CONVERT(date, CHECKTIME)";

$letsgo = odbc_exec($conn, $query);

if ($letsgo === false)
{
    die(print_r( odbc_error(), true));
}

header("location: index.php");

?>

is any idea for this solution ??

CodePudding user response：

There's no such thing as CONVERT in Access SQL.

Instead, each conversion has a separate function, e.g. CDate, CInt, CStr, etc.

Swap CONVERT(date, with CDate(. Note that date conversions in Access assume either yyyy-mm-dd or mm/dd/yyyy.

See https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions for the full list of conversion functions.