One criteria too many?

[apsrbstar]

Hi all.

I am running SX.e 5.6.063 with country pack 1.4.063, on version 9.1e (please, no flames about the version!). I am trying to do a query via VBA for MS Access 2000. This query works:

SELECT oeeh.orderno AS orderno, oeeh.ordersuf AS ordersuf, oeeh.transtype AS transtype, oeel.shipprod AS shipprod, oeel.restockamt AS restockamt, oeel.restockfl AS restockfl, oeel.qtyship AS qtyship, IFNULL(icss.csunperstk,1) AS csunperstk, oeel.netamt AS netamt, oeel.prodcost AS linecost, IFNULL(pder.rebateamt,0) AS rebateamt
FROM pub.oeeh AS oeeh
LEFT JOIN pub.oeel AS oeel
ON oeeh.cono = oeel.cono AND oeeh.orderno = oeel.orderno AND oeeh.ordersuf = oeel.ordersuf
LEFT OUTER JOIN pub.pder AS pder
ON oeel.cono = pder.cono AND oeel.orderno = pder.orderno AND oeel.ordersuf = pder.ordersuf AND oeel.lineno = pder.lineno
LEFT JOIN pub.icss AS icss
ON oeel.cono = icss.cono AND oeel.shipprod = icss.prod AND oeel.icspecrecno = icss.icspecrecno
WHERE oeeh.cono = 1 AND oeel.qtyship > 0 AND oeel.specnstype<>'l' AND oeel.shipprod='CBL16242YGR100' AND oeel.netamt <> 0​

As soon as I add the following, I get a syntax error

AND oeeh.invoicedt = [HASHTAG]#01[/HASHTAG] May 2014#

~~ OR ~~

AND oeeh.invoicedt = #01/05/14#
​

Can anyone suggest what to do about it? I have tryied some bracketting options, such as

SELECT oeeh.orderno AS orderno, oeeh.ordersuf AS ordersuf, oeeh.transtype AS transtype, oeel.shipprod AS shipprod, oeel.restockamt AS restockamt, oeel.restockfl AS restockfl, oeel.qtyship AS qtyship, IFNULL(icss.csunperstk,1) AS csunperstk, oeel.netamt AS netamt, oeel.prodcost AS linecost, IFNULL(pder.rebateamt,0) AS rebateamt
FROM (((pub.oeeh AS oeeh
LEFT JOIN pub.oeel AS oeel
ON oeeh.cono = oeel.cono AND oeeh.orderno = oeel.orderno AND oeeh.ordersuf = oeel.ordersuf)
LEFT OUTER JOIN pub.pder AS pder
ON oeel.cono = pder.cono AND oeel.orderno = pder.orderno AND oeel.ordersuf = pder.ordersuf AND oeel.lineno = pder.lineno)
LEFT JOIN pub.icss AS icss
ON oeel.cono = icss.cono AND oeel.shipprod = icss.prod AND oeel.icspecrecno = icss.icspecrecno)
WHERE oeeh.cono = 1 AND oeel.qtyship > 0 AND oeel.specnstype<>'l' AND oeel.shipprod='CBL16242YGR100' AND oeel.netamt <> 0 AND oeeh.invoicedt = [HASHTAG]#01[/HASHTAG] May 2014#​

 

[tamhas]

Where did you come up with hash signs as the delimiter for the date? Have you tried it with no delimiters?

 

[apsrbstar]

I'm sure I've used hashes before successfully, but I think have found the problem - it should be TO_DATE('mm/dd/yyyy').

 

[TomBascom]

You can choose to consider this comment a "flame" but 9.1e *is* ancient, obsolete and unsupported. You should upgrade. All of the SX.e sites that I have seen come with encrypted source so upgrading is fairly straight-forward. Are you still running Windows 98 on your desktops?