access date functions

[jmore]

I need help with creating a query that will take the current date and return the data from the previous week - I cannot use [start date] [end date] in my criteria because I need to create a crosstab query after I have the one week of data selected....any ideas on how I may be able to get this done?

 

[F15]

If you are able to use the current date, then you can use the following SQL code:

WHERE (((Daily.Date) Between DateAdd("d",-6,Date()) And Date()));

This code will capture the previous 7 days from today's date.
You may also use "y" for year, "m" for month, "ww" for week. However, using the d function provides you with tighter control over how many days you pick up.

 

[jmore]

got another function to work - now the problem is getting the data arranged the way I want it arranged...

Weekly Report: Format(DateAdd("ww",DatePart("ww",[Date],1,3),#1/1/2002#),"mm/dd/yyyy&quot

this function will seperate the dates in my db weekly starting from 1-2002...

the problem I'm having now is I have 12 columns that need to be summed and then ordered by the date and then by another column:
eg field1, field2, field3, field4, field5..etc...
each one of these fields needs to have a total and sorted by the date and then by type....the type for instance would be stored in field6 (e.g.)....but I want all the totaled fields under fields6 - making field six some what of a header - tried doing a crosstab.... but having some problems keeping the data grouped right in the report - not sure if any of this will make any sense...