Report Based on Dates

[Ryan Langton]

I have some global variables (varStartDate and varEndDate). I want my
report to display data between those dates. However, when I try to use
those variables in the MS Access query, I get an error. I could use them
with VB code, but I get an error when trying to update the Reports source
(though it would work fine on a form). Whats the best way to pass variables
to a Report and have it query it's data based on those variables?

 

[Larry]

The way I typically do it is to create public functions, which capture
the global variable, then use that function in the query on the
criteria line.

' Create a function like this for Start/End dates
Public Function GetStartDate() As DateTime
GetStartDate = varStartDate ' This assumes "varStartDate" is global.
End Function

In the criteria, under the date you are checking use something like:
Between GetStartDate() And GetEndDate()

Make sure you put the () after the function names, to let the query
know they are functions.

HTH

 

[KARL DEWEY]

I pass the dates through the query by not only entering them in the criteria
row but in the field row. Just put your label, colon, and a pasted copy of
the criteria. I copy and paste to make sure they are the same. It would
look like --
Start Date: [Enter start date] End Date: [Enter end date]

 

[Ryan Langton]

I did this and am getting an error:
'GetStartDate' is not a recognized function name.
Even though I have tried the function (exactly as you have it typed) in both
my globals module, and this particular report module.
This is an .adp if that makes any difference (though I wouldn't think that
it would).

 

[Larry]

I'm afraid I've never done anything with an .adp, but I wouldn't think
that would matter.

The only suggestions I can give at this point, without seeing the code,
is to make sure the code is defined as public, spelled correctly and
that you use the parenthesis at the end of the function name in the
criteria (otherwise Access doesn't recognize it as a function).

If you'd like me to take a closer look at the code, I'll be happy to.
Just zip it up and send it to me at my hotmail email account. The
address is llj69_hotmail_com. (replace the underscores with the proper
symbols, of course)

 

[Brendan Reynolds]

The fact that the app is an ADP most certainly does make a difference - you
can't call custom VBA functions from a query in an ADP.

 

[Brendan Reynolds]

BTW: Take a look at the 'Sales by Year' report in the 'NorthwindCS.adp'
sample app for an example of how to do this.

 

[Larry]

WOW, thanks for that info Brendan. I've never worked with an ADP, so
this is big news to me.

Learn something new every day.