SQL Syntax error missing operator in query expression - Ms Access DB

Question

I have code in PHPRunner ( after record updated ) to Update a master table with the total number of hours worked based on the details table (multiple entries ) but when I add a sencond WHERE clause into the $strUpdate I get the syntax error.

I have change the code multiple times and is not working with the WHERE and the AND clauses. If I delete AND (Date=$values[DateField1]) and remove the () it calculates the totals, the only problem is that it adds the total to all my master tables Hours fields instead of the one for that date only.

$mykey=$values["OwnerID"];
$mykey1=$values["DateField1"];

global $conn;

$str = "select SUM(Hours)AS [Hrs] FROM Attendance WHERE OwnerID='{$mykey}' Group by DateField1";

$rs = db_query($str);
$value = db_fetch_numarray($rs);

$total=$value[0];

$strUpdate = "update HrsOT set `DailyHrs`='{$total}' Where ((OwnerID='{$mykey}') AND (Date=$values[DateField1])";

db_exec($strUpdate,$conn);
return true;

I have look at sample codes and read sql / msaccess query pages online and cant find the reason for the error. Any help is very much appreciated

score 0 · Answer 1 · answered Sep 23 '21 at 08:53

0

2 WHERE clauses

That's what the mistake (or) issue here. You can't have 2 WHERE clause in a single SQL statement unless it's a subquery or other form. Rather try compounding the conditions using AND OR conditional operators.

answered Sep 23 '21 at 08:53

Rahul

76,197
13
71
125

If you look at the code above you will see a WHERE clause and a AND clause. The problems is that once I include the AND clause to the query, I get the error. – Derek Sep 23 '21 at 20:56

Parfait · Answer 2 · 2021-09-24T15:39:27.003

0

Simply parameterize the date value. Right now, you include the variable (known only in PHP) $values[DateField1] directly in the SQL statement unlike your first two variables where you interpolate them: $total and $mykey. Therefore, in running this query, the Access engine does not recognize this entity.

However, if you parameterize (avoid concatenation) with prepared statements, you can effectively pass application layer values to database layer. Below is a PHPRunner version of prepared statements. Additionally, for MS Access, string dates are not evaluated without conversion with CDate or enclosing literals with octothorpes, #. Below assumes $total and $mykey are numbers.

$sql = DB::PrepareSQL(
    "UPDATE HrsOT SET [DailyHrs]= :1 WHERE ((OwnerID = ':2') AND ([Date] = CDate(':3'))", 
    $total,
    $mykey, 
    $values[DateField1]
);

DB::Exec( $sql );

edited Sep 24 '21 at 15:39

answered Sep 23 '21 at 21:17

Parfait

104,375
17
94
125

Thank you, I was able to fix the query and I don't get any error but the table is not updating. I did print_r the sql query and I get. UPDATE HrstOT SET 'DailyHrs'=3 WHERE ((OenerID='admin' and ([Date]=CDate('2021-09-24 00:00:00')){'Sucess";true"message":"<<< Record updated >>> I am puzzled. Any suggestions on how to debug this – Derek Sep 24 '21 at 06:06
As stated, above assumes parameters, `$total` and `$mykey` are numbers. If any are strings, enclose the `:1` or `:2` placeholders with single quotes in SQL statement. From your output, `OwnerID` appears to be string type. Also `[Date]` is assumed to be date/time type. – Parfait Sep 24 '21 at 15:39

score 0 · Answer 3 · answered Sep 24 '21 at 10:40

I could not make the update query work, all seem fine but the table was not getting updated, after multiples times I change my table, so I created a new linking field between the master and the detail table and simplified my query by removing the AND clause. all working and posted here for anyone interested.

$mykey=$values["MasterID"];

global $conn;

$str = ("SELECT SUM(Hours) FROM Attendance WHERE 
MasterID=$mykey"
);
$rs = db_query($str,$conn);
$value = db_fetch_numarray($rs);

$total=$value[0];

$sql = ("UPDATE HrsOT SET DailyHrs='$total' WHERE 
ID=".$values["MasterID"]);
DB::Exec($sql,$conn);

return true;

SQL Syntax error missing operator in query expression - Ms Access DB

3 Answers3