Access Macro with changing date range

[kmacd6951]

New to Access - hate that you can't do Macro's like you can in Excel (record key strokes)... anyway.

Is there a way to create a macro that would 1).open my query, 2).enter the date range (I get two pop up screens for FROM and THRU date currently). The date range would need to be Yesterday's date thru Today's date. 3). Give me the output in Pivot Table view.

Does someone have an example of this or something similar that I could work from?

Thanks!
Katie

 

[mdmilner]

Actually, you can do this all from a single Crosstab query. And, the query can automatically figure out 'yesterday' without the use of any code/macros.

Here's a quick sample produced by QBE (query by example wizard interface)
It makes the assumption that you ALWAYS use yesterday as the start date and that nothing is future dated. If anything can be future dated, just expand the 'WHERE' condition to specify a maximum value.

This uses the built in 'Date' function that is equal to today's system date.

TRANSFORM Count(tblData.File) AS CountOfFile
SELECT tblData.Commodity, Count(tblData.File) AS [Total Of File]
FROM tblData
WHERE (((tblData.Datefld)>=Date()-1))
GROUP BY tblData.Commodity
PIVOT tblData.C;

As an alternative, you can borrow something like this that figures out the prior workday (copy into a module) - this is code.

http://www.mvps.org/access/datetime/date0012.htm

And then change your query to

TRANSFORM Count(tblData.File) AS CountOfFile
SELECT tblData.Commodity, Count(tblData.File) AS [Total Of File]
FROM tblData
WHERE (((tblData.Datefld)>=dhPreviousWorkdayA(Date())))
GROUP BY tblData.Commodity, tblData.Date
PIVOT tblData.C;

This doesn't allow you to type in a date, but would cover you running this on Monday to reflect Friday Data, if that is what you need. If you'd like to type in dates, you'll end up using InputBoxes which are used to 'build' a query which you save as a QueryDef

Mike

 

[kmacd6951]

So, does this mean I should recreate my current query as a crosstab query instead? or do I add in the TRANSFORM line to the top of the SQL of my current query?

Thank you!

 

[mdmilner]

Recreate is best. Lets you take advantage of the wizard interface to help build it.

 

[kmacd6951]

So... what if I want to set up the Macro to run for 7 days back through today?

I tried to use the Action of ApplyFilter and my Where Condition says:

Between [Cases Opened Query]![SWDATECREATED] (Date () -7) And [Cases Opened Query]![SWDATECREATED] Date ()

But it tells me this is invalid. Actually it says that MS Access can't Parse the expression
Does anyone know what I am missing??

 

[mdmilner]

This should work. Between should be unnecessary unless you're attempting to do something like pick a 7 day time period from a month ago.

WHERE (((tblData.Datefld)>=Date()-7))

You're in one of my experience 'grey' areas. I know what works for me, but never worried about the precise reason why it doesn't work.

What I've noticed is sometimes, when building complex expressions within a Crosstab (TRANSFORM) query, Access can not handle it. I think this is due to how SQL was implemented within Access (kinda half-azzed sometimes)

A workaround is to do this in two steps. Build a select query that does your manipulations and then base the Crosstab query off it. If that doesn't work, I tend to start fiddling until I get something to work.

Mike