Help with dates

  • Thread starter Thread starter Lou
  • Start date Start date
L

Lou

I am new to Access and have no experience with working
with formulas in queries. I need help in figuring out
what criteria to use for pulling how many orders have
been placed for the current week and the month..
I will appreciate any help that anyone can offer.
Lou
 
Lou,

It would not be easy to get the weekly total and the monthly total in a
single query. Try it as two separate queries.

Make a query based on your Orders table. Put whatever is your primary
key field (OrderNumber?) into the query design grid. In the monthly
query, put this in a new column in the grid...
OrderMonth: Format([OrderDate],"yymm")
.... and in the criteria of this column, put Format(Date(),"yymm")
Make it a Totals Query (select Totals from the View menu). In the
Totals row of the OrdewrNumber field, put Count, and in the Totals row
of the calculated date field, put Where.

Obviously, you need to change the above to use your own actual field names.

In the weekly total query, make your calculated field like this...
OrderWeek: Year([OrderDate]) & DatePart("ww",[OrderDate])
.... and similarly, in the criteria, put...
Year(Date()) & DatePart("ww",Date())
 
Steve,
I want to thank you very much for you help. Your
instruction were very clear and that was GREAT..
Thank again.
Lou
-----Original Message-----
Lou,

It would not be easy to get the weekly total and the monthly total in a
single query. Try it as two separate queries.

Make a query based on your Orders table. Put whatever is your primary
key field (OrderNumber?) into the query design grid. In the monthly
query, put this in a new column in the grid...
OrderMonth: Format([OrderDate],"yymm")
.... and in the criteria of this column, put Format(Date (),"yymm")
Make it a Totals Query (select Totals from the View menu). In the
Totals row of the OrdewrNumber field, put Count, and in the Totals row
of the calculated date field, put Where.

Obviously, you need to change the above to use your own actual field names.

In the weekly total query, make your calculated field like this...
OrderWeek: Year([OrderDate]) & DatePart("ww",[OrderDate])
.... and similarly, in the criteria, put...
Year(Date()) & DatePart("ww",Date())

--
Steve Schapel, Microsoft Access MVP

I am new to Access and have no experience with working
with formulas in queries. I need help in figuring out
what criteria to use for pulling how many orders have
been placed for the current week and the month..
I will appreciate any help that anyone can offer.
Lou
Click to expand...
.
Click to expand...
 
Back
Top