Well, while hunting I found this syntax that worked for my query.

WHERE SO_SalesOrderDetail.ItemCode='S-205180' AND SO_SalesOrderHeader.OrderDate> {d'2022-06-01'}

The {d'2022-06-01'} solved this problem.

Select tbl.column, tbl.column....
FROM tbl
WHERE SO_SalesOrderHeader.OrderDate>'2023/01/28'

attempting to get records > than '2023'01'28' below error

[ProvideX][ODBC Driver]Invalid operand for operator: >

Is there a syntax here I'm missing?

Very best regards,
Bret

Using a select against a table column in DATE FORMAT.
SO_SalesOrderHeader.OrderDate

returns this 9/30/2020 12:00:00 AM
Any way to format the return value as?  9/30/2020
I have the ODBC manual, but can't figure it out.
Thanks

I'm writing a powershell script to run the following query, but I get the lexical identifier error.

Original SQL script:
SELECT SO_SalesOrderDetail.SalesOrderNo, SO_SalesOrderHeader.OrderType, SO_SalesOrderHeader.OrderStatus, SO_SalesOrderDetail.LineKey, SO_SalesOrderDetail.ItemCode, SO_SalesOrderDetail.ItemCodeDesc, SO_SalesOrderDetail.PromiseDate, SO_SalesOrderDetail.PurchaseOrderNo, IM_ItemWarehouse.QuantityOnHand, IM_ItemWarehouse.QuantityOnPurchaseOrder, IM_ItemWarehouse.QuantityOnSalesOrder, IM_ItemWarehouse.WarehouseCode, IM_ItemWarehouse.BinLocation
FROM (SO_SalesOrderDetail INNER JOIN SO_SalesOrderHeader ON SO_SalesOrderDetail.SalesOrderNo = SO_SalesOrderHeader.SalesOrderNo) INNER JOIN IM_ItemWarehouse ON SO_SalesOrderDetail.ItemCode = IM_ItemWarehouse.ItemCode
WHERE (((SO_SalesOrderDetail.ItemCode)="S-209220"));



My attempt at adding the lexical correct version using oj:

"SELECT SO_SalesOrderDetail.SalesOrderNo, SO_SalesOrderHeader.OrderType, SO_SalesOrderHeader.OrderStatus, SO_SalesOrderDetail.LineKey, SO_SalesOrderDetail.ItemCode, SO_SalesOrderDetail.ItemCodeDesc, SO_SalesOrderDetail.PromiseDate,
SO_SalesOrderDetail.PurchaseOrderNo, IM_ItemWarehouse.QuantityOnHand, IM_ItemWarehouse.QuantityOnPurchaseOrder, IM_ItemWarehouse.QuantityOnSalesOrder, IM_ItemWarehouse.WarehouseCode, IM_ItemWarehouse.BinLocation
FROM (SO_SalesOrderDetail {oj SO_SalesOrderHeader ON SO_SalesOrderDetail.SalesOrderNo = SO_SalesOrderHeader.SalesOrderNo)} {oj LEFT INNER JOIN IM_ItemWarehouse ON SO_SalesOrderDetail.ItemCode = IM_ItemWarehouse.ItemCode}
WHERE (((SO_SalesOrderDetail.ItemCode)='S-209220'));"


Best regards

We use Sage 100 which uses a ProvideX backend DB running on a virtual 2012 server VM
Clients tested Windows 10 64, Windows 7 64

We access Sage 100 using the SOTOMAS ODBC driver

Under Lazarus/Delphi pascal program
Here's the problem.
When a Sage 100 client is running on a client machine, we have a 4 second delay before an ODBC connection completes.
When a Sage 100 client is NOT running on a client machine, we have a 1 second ODBC connection time.

Access can make the connection in 1 second regardless of the Sage client running or not.

Any ideas why?