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
FROM Cardex
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.

Thanks

>>This is done via some VBA code in the Report Header/OnFormat event.<<
There is no need to use Event + VBA code. The expressions:

DateAdd("d", - WeekDay(Date(),1) - 6, Date())

and

DateAdd("d", - WeekDay(Date(),1), Date())

give the date value of the Sunday and Saturday of the previous week.

Use the above expressions in the WHERE clause of your SQL and as the ControlSources of your TextBoxes in the ReportHeader section.

BTW, the correct selection criteria shoud be a WHERE clause, not a HAVING clause, i.e.


I don't think the DISTINCT predicate as per your posted SQL is necessary, either. I have removed it from my suggested SQL but you need to check that the SQL returns the data you want.

Finally "Date" is a reserved word (for the Date() function I use) and should not be used as a Field name. Using reserved words as custom names in your database can create unnecessary complications and quite often, hard-to-trace problems.

Thank you very much VTDinh, That was exactly perfect to fix my problem (and educate me as well)