I am fairly new to Access, so please bear with me.
I have a report generated that populates 2 text boxes that contain dates for the start and end of a weeks period. This is done via some VBA code in the Report Header/OnFormat event. The dates are calculating perfectly (Sunday to Saturday of the week previous). What I need is a way to base a query for the detail area of the report that uses those dates as the start and end of the period and then only display those transactions that happened between those 2 dates. How can I get the values of those 2 dates into the query?
This is what I have managed to come up with so far, but I can not get the values from the textboxes on the report into this statement. I need the values for TextboxStart and TextboxEnd that are on a report called "Weekly Usage". Everything else works fine.
SELECT DISTINCT Cardex.Vendor, Cardex.ItemNum, Cardex.Description, Sum(Cardex.Quantity) AS SumOfQuantity, Cardex.UoM, Cardex.TransactionType, Cardex.Date
GROUP BY Cardex.Vendor, Cardex.ItemNum, Cardex.Description, Cardex.UoM, Cardex.TransactionType, Cardex.Date
HAVING (((Cardex.TransactionType)="Issue") AND ((Cardex.Date) Between ([TextboxStart]) And ([TextboxEnd])))
ORDER BY Cardex.Vendor;
Please point me in the right direction.