Date restrictions in Access 2

[wbishopjr]

Is there an easy way to query a table of dated events by week? Access offers sum by day, month, quarter, & year, but not week. I have a table that people enter data in each day, but I want the results exported to Excel totaled from Monday to Sunday of each week.

Also, a sidebar question, can you execute a macro in Excel from Access? I want to configure one button on my switchboard form to export this data to Excel and create a decent chart.

Thanks a million
Waymond

 

[cheerio]

Use the DateDiff function.

Example: DateDiff("w",DateSerial(2001,12,31),[order Date])

This will count weeks into the current year starting at week 0 for 1st Jan. You can change your starting date to change the base.

Ken

 

[DaleWatson123321]

Hi Waymond,

I've created an example file for you. Please email me to let me know when you're back to work, and I'll send you the file.

I hope you find this solution useful. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[wbishopjr]

Thank you both for your suggestions!!! Both were great ideas.
Dale, your file was unbelievable! I would like to ask you a question if you don't mind. I was trying to "reverse engineer" the thing and found the "=DCOUNTA(data,3,wk_1)" formula. How does that work, it looks pretty powerful if I can grasp it. It references row 3 of data, and wk_1 date restriction. Row 3 is BIC and the date restriction wk_1 works even though Excel has "#NAME?" in the "wk_1" range. I don't understand why it still works...but it's pretty COOL anyway!
Thanks for any light you could shed, and regarding your e-mail, Canadians are pretty great, too! I think that our countries compliment each other very well. Thanks for the kind words about the good 'ol USA.

Waymond