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

 

[Guest]

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).

 

[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.