Home > Blockchain >  odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in

Time:09-29

I have some Error when queryin in Microsoft Access

I got error "Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'CHECKTIME BETWEEN ts '2021-08-01'} AND ts '2021-08-30'}'., SQL state 37000 in SQLExecDirect in C:\laragon\www\mondelez-report-access\getLog.php on line 38 37000" when trying to insert Microsoft access.

this is my code in getLog.php

<?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 TransactionLog (USERID, CHECKTIME, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID)
    SELECT CHECKINOUT.USERID, CHECKINOUT.CHECKTIME, USERINFO.Name, USERINFO.lastname, USERINFO.CardNo, DEPARTMENTS.DEPTID, DEPARTMENTS.DEPTNAME, DEPARTMENTS.SUPDEPTID
    FROM
    (CHECKINOUT
    LEFT JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID)
    LEFT JOIN DEPARTMENTS ON DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID
    WHERE CHECKTIME BETWEEN {ts '" . $from_date . "'} AND {ts '" . $to_date . "'} ";

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

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

header("location: logTransaction.php");

?>

and this is my view for progress getLog.php

<?php
// For starting Session in php
session_start();
// We check if user login or not
// Check with this syntax
if (isset($_SESSION['id'])) {
?>

    <DOCTYPE html>
        <html>

        <head>
            <meta charset="UTF-8">
            <title>Mondelez International</title>
            <link rel="shortcut icon" href="../mondelez-report/assets/img/mondelez-logo.png" />
            <!-- CSS Load Area -->
            <link rel="stylesheet" href="assets/plugins/bootstrap/css/bootstrap.min.css" type="text/css" />
            <link rel="stylesheet" href="assets/plugins/font-awesome/css/font-awesome.min.css" type="text/css" />
            <!-- CSS Custom Load Area -->
            <link rel="stylesheet" href="assets/css/mondelez.css" type="text/css" />
        </head>

        <body>
            <?php
            include 'database.php';
            ?>
            <!-- Navigation Bar -->
            <nav class="navbar navbar-default" style="background-color: var(--mondelez-white); border-color: var(--mondelez-primary);">
                <div class="container">
                    <!-- Brand and toggle get grouped for better mobile display -->
                    <div class="navbar-header">
                        <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
                            <span class="sr-only">Toggle navigation</span>
                            <span class="icon-bar"></span>
                            <span class="icon-bar"></span>
                            <span class="icon-bar"></span>
                        </button>
                        <a class="navbar-brand" href="index.php">
                            <img alt="Brand" src="../mondelez-report/assets/img/mondelez-logo.png" alt="logo" class="img img-responsive" style="width: 100px; height: 100px; margin-top: -40px;">
                        </a>
                    </div>
                    <!-- Collect the nav links, forms, and other content for toggling -->
                    <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
                        <ul class="nav navbar-nav">
                            <li><a href="index.php">Calculate Data</a></li>
                            <li><a href="logTransaction.php">Transaction Log</a></li>
                            <li class="dropdown">
                                <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Reports <span class="caret"></span></a>
                                <ul class="dropdown-menu">
                                    <li><a href="attReports.php">Attendance Reports</a></li>
                                    <li><a href="wrkReports.php">Working Days Reports</a></li>
                                    <li><a href="trxReports.php">Transaction Log Reports</a></li>
                                </ul>
                            </li>
                        </ul>
                        <ul class="nav navbar-nav navbar-right">
                            <li class="dropdown">
                                <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">admin<span class="caret"></span></a>
                                <ul class="dropdown-menu">
                                    <li><a href="login.php">Logout</a></li>
                                </ul>
                            </li>
                        </ul>
                    </div><!-- /.navbar-collapse -->
                </div><!-- /.container-fluid -->
            </nav>
            <!-- Navigation Bar End -->
            <!-- Header -->
            <div class="container">
                <div class="row">
                    <div class="col-md-12">
                        <img class="mondelez-box-home-and-reports-container-image" src="../mondelez-report/assets/img/mondelez-logo.png" alt="logo" />
                    </div>
                    <div class="col-md-12">
                        <span class="mondelez-box-home-and-reports-container-text">Transaction Log</span>
                    </div>
                </div>
            </div>
            <!-- Header End -->
            <br />
            <!-- Form Reports -->
            <div class="container">
                <div class="row">
                    <div class="col-md-12">
                        <form action="getLog.php" method="post" id="sample_form">
                            <div class="row">
                                <div class="col-md-6">
                                    <div class="form-group">
                                        <label>Period From : </label>
                                        <input type="date" name="from_date" required="required" class="form-control">
                                    </div>
                                </div>
                                <div class="col-md-6">
                                    <div class="form-group">
                                        <label>Period To : </label>
                                        <input type="date" name="to_date" required="required" class="form-control">
                                    </div>
                                </div>
                                <div class="col-md-12">
                                    <div class="form-actions">
                                        <button type="submit" class="btn btn-primary" style="float: right;" name="submit">
                                            <i class="fa fa-download" style="padding-right: 5px;"></i>
                                            Get Log
                                        </button>
                                    </div>
                                </div>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
            <!-- Form Reports End -->
            <!-- Footer -->
            <div class="container-fluid">
                <div class="row">
                    <div class="col-md-12">
                        <div class="footer">
                            Powered by <a class="no-link" href="https://asiasekuriti.co.id/" target="_blank">PT Asia Sekuriti Indonesia.</a>
                        </div>
                    </div>
                </div>
            </div>
            <!-- Footer End -->
            <!-- JS Load Area -->
            <script src="assets/plugins/jquery/jquery.min.js"></script>
            <script src="assets/plugins/bootstrap/js/bootstrap.min.js"></script>
            <!-- JS Custom Load Area -->
            <script src="assets/js/mondelez.js"></script>
        </body>

        </html>
    </DOCTYPE>

<?php
} else {
    echo "Invalid Request";
}
?>

please help, I already try many tutorial for fix this issue but still not working

CodePudding user response:

Date values in Access are not text, so your code should result in a finished expression like this:

WHERE CHECKTIME BETWEEN #2021/01/31# AND #2021/12/31#;
  • Related