Auto populating a user defined date range to a report

[dozingquinn]

Hello,

Is there any way to auto populate the user defined date range into a report? I currently have the criteria "Between [StartDate] And [EndDate]" for a date range field. This prompts the user to enter a date range before the report is visible.

Is it possible to capture this date range and display it on the report?

I am using Access 97

Thanks!

[BradHodge]

I would take the criteria out of your query and instead attach the code (below) to a button on a form. This should do what you are looking for.

Hollar if you have questions.

Brad.

Dim strLinkCriteria As String
Dim BeginDate As String
Dim EndDate As String
 
BeginDate = InputBox("What date would you like to start with?")
EndDate = InputBox("What date would you like to end with?")
 
strLinkCriteria = "([DateField] BETWEEN #" & BeginDate & "# AND #" & EndDate & "#)"
DoCmd.OpenReport "ReportName", acViewPreview, , strLinkCriteria

[DAHMB]

As Brad said the best way is to start the report from a form with the criteria in the form as he posted above. Then in your report you just do an expression in the controlbox of the fields that you want to 'auto populate" with the span of dates searched, that point to the fields in the form you opened your search from.

[JustJim]

As Brad said the best way is to start the report from a form with the criteria in the form as he posted above. Then in your report you just do an expression in the controlbox of the fields that you want to 'auto populate" with the span of dates searched, that point to the fields in the form you opened your search from.

If you are outside of the USA and/or your regional settings are for other than the US standard MM/DD/YYYY date format, you will have to format your dates into that format for the criteria to do what you want.

Jim

[dozingquinn]

Hello again,

Sorry it took me a while to get back to you - I have managed to setup the date message box (thanks!), however as Jim rightly points out -

If you are outside of the USA and/or your regional settings are for other than the US standard MM/DD/YYYY date format, you will have to format your dates into that format for the criteria to do what you want.

What's the best way of altering the date regional settings?

One other question - my report has dcounts setup. The results of this are suppose to vary depending on the date range selected. However the dcounts are currently staying static and totalling the entire query - rather than the selected date range records. Is there a way I can ensure the dcount is linked to only the records selected by the date range?

Thanks again

[JustJim]

Hello again,

Sorry it took me a while to get back to you - I have managed to setup the date message box (thanks!), however as Jim rightly points out -



What's the best way of altering the date regional settings?

One other question - my report has dcounts setup. The results of this are suppose to vary depending on the date range selected. However the dcounts are currently staying static and totalling the entire query - rather than the selected date range records. Is there a way I can ensure the dcount is linked to only the records selected by the date range?

Thanks again

Date/Time regional settings is a Windows thing and if you change it, it will reflect in every programme/application that uses dates and times. Best just to format the date in your search criteria.

Since the entire Domain of your form is limited by the selected date range, you should be able to get away with Count rather than DCount. Otherwilse, make sure the criteria argument of the Dcount reflects the selected date range and that any fields in the criterie argument are contained in the domain argument.

Jim

[dozingquinn]

Thanks Jim.

Unfortunately I couldn't simply use the count function - as I wanted to use some criteria as well. I managed to fix it by using the formula:

Sum(Iif([field]="yes",1,0)

Thanks again for your help - you are super.

[JustJim]

Thanks Jim.

Unfortunately I couldn't simply use the count function - as I wanted to use some criteria as well. I managed to fix it by using the formula:

Sum(Iif([field]="yes",1,0)

Thanks again for your help - you are super.

Ahh the dreaded Iif, well sometimes you just gotta.

Thanks for the kind words, but I assure you I get more questions answered than I answer.

Jim