Software:
Using Access 2000 on Windows 2000 service pack 3, running a System DSN to a Pervasive ODBC Client Interface.
The Access database is linking to tables in the Pervasive Server. I am trying to run a parameter query with the table tixhsthdr on the field ticketdate. The table requires the date be in YYYYDDMM format.
Query:
SELECT DISTINCTROW TixHistHdr.TicketDate, Items.ItemNumber, Items.Description1, Items.Description2, VendorItem.VendorItemNumber, Vendor.ApVendorNumber, Sum(TixHistLin.Quantity) AS [Sum Of Quantity], Sum(TixHistLin.ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM (Items INNER JOIN (Vendor INNER JOIN VendorItem ON Vendor.Vendor = VendorItem.Vendor) ON Items.ItemNumber = VendorItem.ItemNumber) INNER JOIN (TixHistHdr INNER JOIN TixHistLin ON TixHistHdr.TicketNumber = TixHistLin.TicketNumber) ON Items.ItemNumber = TixHistLin.ItemNumber
GROUP BY TixHistHdr.TicketDate, Items.ItemNumber, Items.Description1, Items.Description2, VendorItem.VendorItemNumber, Vendor.ApVendorNumber
HAVING (((TixHistHdr.TicketDate) Between [Date1] And [Date2]) AND ((Vendor.ApVendorNumber)="PUM001"
)
ORDER BY Items.ItemNumber;
If I run the query it will bring up the parameter boxes Date1 and Date2, but when I press OK to finish the query it gives me the error "ODBC -- call failed".
However, if I type out the between statement as follows:
Between "YYYYDDMM" And "YYYYDDMM"
The query returns the proper information.
I need to have the query prompt the user for the date range since this changes every week.
Your help is greatly appreciated.
Using Access 2000 on Windows 2000 service pack 3, running a System DSN to a Pervasive ODBC Client Interface.
The Access database is linking to tables in the Pervasive Server. I am trying to run a parameter query with the table tixhsthdr on the field ticketdate. The table requires the date be in YYYYDDMM format.
Query:
SELECT DISTINCTROW TixHistHdr.TicketDate, Items.ItemNumber, Items.Description1, Items.Description2, VendorItem.VendorItemNumber, Vendor.ApVendorNumber, Sum(TixHistLin.Quantity) AS [Sum Of Quantity], Sum(TixHistLin.ExtendedPrice) AS [Sum Of ExtendedPrice]
FROM (Items INNER JOIN (Vendor INNER JOIN VendorItem ON Vendor.Vendor = VendorItem.Vendor) ON Items.ItemNumber = VendorItem.ItemNumber) INNER JOIN (TixHistHdr INNER JOIN TixHistLin ON TixHistHdr.TicketNumber = TixHistLin.TicketNumber) ON Items.ItemNumber = TixHistLin.ItemNumber
GROUP BY TixHistHdr.TicketDate, Items.ItemNumber, Items.Description1, Items.Description2, VendorItem.VendorItemNumber, Vendor.ApVendorNumber
HAVING (((TixHistHdr.TicketDate) Between [Date1] And [Date2]) AND ((Vendor.ApVendorNumber)="PUM001"
)ORDER BY Items.ItemNumber;
If I run the query it will bring up the parameter boxes Date1 and Date2, but when I press OK to finish the query it gives me the error "ODBC -- call failed".
However, if I type out the between statement as follows:
Between "YYYYDDMM" And "YYYYDDMM"
The query returns the proper information.
I need to have the query prompt the user for the date range since this changes every week.
Your help is greatly appreciated.
