By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Auto populating a user defined date range to a report

P: 28
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!
Dec 5 '07 #1
Share this Question
Share on Google+
7 Replies


BradHodge
Expert 100+
P: 166
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strLinkCriteria As String
  2. Dim BeginDate As String
  3. Dim EndDate As String
  4.  
  5. BeginDate = InputBox("What date would you like to start with?")
  6. EndDate = InputBox("What date would you like to end with?")
  7.  
  8. strLinkCriteria = "([DateField] BETWEEN #" & BeginDate & "# AND #" & EndDate & "#)"
  9. DoCmd.OpenReport "ReportName", acViewPreview, , strLinkCriteria
Dec 5 '07 #2

100+
P: 147
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.
Dec 5 '07 #3

JustJim
Expert 100+
P: 407
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
Dec 6 '07 #4

P: 28
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
Dec 10 '07 #5

JustJim
Expert 100+
P: 407
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
Dec 10 '07 #6

P: 28
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.
Dec 11 '07 #7

JustJim
Expert 100+
P: 407
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
Dec 11 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.