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

Hi,



It would be nice to see the code that generates the dates but you might try the following:

... Between Reports![Weekly Usage].TextboxStart And Reports![Weekly Usage].TextboxEnd ...

I'm not sure if that will work, though. You might get a "circular reference" error.

Hope that helps...

>>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)

Nice work, Van.

Would you mind explaining why it has to be a WHERE and not HAVING?

You're welcome... Glad we could help...

Hi theDBguy

It does not have to be since JET seems to be able to optimize and use the HAVING clause as if it were a WHERE clause for efficiency whenever possible. However, I still go for the more standard usage of WHERE and HAVING since I use both JET/ACE SQL and T-SQL.

In standard usage, the WHERE conditions are evaluated before grouping/totalling while the HAVING conditions are evaluated after grouping/totalling. In a Query with large number of possible rows, we certainly want to reduce the number of rows as soon as we can and before grouping/totalling, i.e. the WHERE clause, for efficiency (instead of grouping/totaling a large number of rows and then eliminating some of the grouped/totaled rows which would happen, in theory, if the same conditions are used in the HAVING clause).

Thus, in theory, the WHERE clause is always more efficient than the HAVING clause with the same conditions and use the WHERE clause whenever you can.

However, there are cases where we want to place conditions on the totaled values, i.e. the conditions have to be applied after grouping/totalling and these have to be handled by the HAVING clause and not the WHERE clause since the totalled values are not available prior to grouping/totalling.

The simple rule is that if the condition involves a totalled value, use HAVING clause. Otherwise, use WHERE clause.

Thanks, Van. Makes sense!