I have added a COMMAND to a crystal report and it fails on the DATE() 1

[rwn]

within this command is this:
SELECT SO_Detail.Material, SO_Detail.SO_Line, SO_Detail.Deferred_Qty, SO_Detail.Backorder_Qty, SO_Detail.Shipped_Qty, Packlist_Detail.Quantity, Delivery.Shipped_Date, SO_Header.Customer_PO
FROM ((SO_Detail INNER JOIN Packlist_Detail ON SO_Detail.SO_Detail = Packlist_Detail.SO_Detail) INNER JOIN Delivery ON Packlist_Detail.SO_Detail = Delivery.SO_Detail) INNER JOIN SO_Header ON SO_Detail.Sales_Order = SO_Header.Sales_Order
WHERE (((SO_Detail.Status) In ('backorder','shipped')) AND ((Delivery.Shipped_Date) Between Date()-13 And Date()));

The overall select statement works except when I add in the date() range.

 

[kray4660]

First off, what is your data source. I do not know what database sources use a Date() (I am assuming it is supposed to return the current date).

 

[rwn]

it is an odbc connection to a SQL Database.

 

[CoSpringsGuy]

replace date() with getdate()

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[rwn]

I replaced as shown below: (it STILL FAILS with this message: Failed to retrieve data from the database. Details: 42000:[microsoft][ODBC SQL Server Driver)[SQL Server[Incorrect Syntax near '].[Database Vendor Code:102]

SELECT SO_Detail.Material, SO_Detail.SO_Line, SO_Detail.Deferred_Qty, SO_Detail.Backorder_Qty, SO_Detail.Shipped_Qty, Packlist_Detail.Quantity, Delivery.Shipped_Date, SO_Header.Customer_PO
FROM ((SO_Detail INNER JOIN Packlist_Detail ON SO_Detail.SO_Detail = Packlist_Detail.SO_Detail) INNER JOIN Delivery ON Packlist_Detail.SO_Detail = Delivery.SO_Detail) INNER JOIN SO_Header ON SO_Detail.Sales_Order = SO_Header.Sales_Order
WHERE (((SO_Detail.Status) In ('backorder','shipped')))
AND ((Delivery.Shipped_Date) Between getdate()-13 And getdate()));

 

[rwn]

found the issue, one to many ).

SELECT SO_Detail.Material, SO_Detail.SO_Line, SO_Detail.Deferred_Qty, SO_Detail.Backorder_Qty, SO_Detail.Shipped_Qty, Packlist_Detail.Quantity, Delivery.Shipped_Date, SO_Header.Customer_PO
FROM ((SO_Detail INNER JOIN Packlist_Detail ON SO_Detail.SO_Detail = Packlist_Detail.SO_Detail) INNER JOIN Delivery ON Packlist_Detail.SO_Detail = Delivery.SO_Detail) INNER JOIN SO_Header ON SO_Detail.Sales_Order = SO_Header.Sales_Order
WHERE (((SO_Detail.Status) In ('backorder','shipped')))
AND ((Delivery.Shipped_Date) Between getdate()-13 And getdate());