Query Date Criteria

[AWilderbeast]

Hi,
I have a date field in a query and was wondering if anyone knows how i can
have a prompt to choose between two dates.

Thanks
Alex

 

[Crispy]

Hi. In your date field in the query grid, type the following in the Criteria cell: Between [Enter Start Date] and [Enter End Date]

This will prompt the user to specify the date range. If it's just one date you want to specify, just type [Enter Date] in the criteria cell.

Hope this helps

 

[KSReynolds]

Is there a way, using this method, to have the input boxes that are generated by the parameter put in the / / for the date input?

 

[pr2-eugin]

KSReynolds, Your Question is q bit unclear.. What are you asking exactly?

BTW, please can you start a new thread for New Queries next time?

 

[KSReynolds]

I have a date value in my query that controls the Reports output. In the date field, I have entered Between [Enter Start Date] and [Enter End Date].

When the user is prompted for the date, there are no formatting options applied to the prompt, so the user has to enter in the "/" characters. I was wondering if there was a way to have these characters for inputting the date field populated without creating an unbound form.

 

[pr2-eugin]

You could create your own Form and then have two controls - format set to short date and then Run the Report from the button click of this form.. Just make sure you update the Query to feed from the Forms!FormName!StartDateControlName and Forms!FormName!EndDateControlName.

 

[KSReynolds]

I was hoping that I wouldn't have to take that route because I will have to create individual forms for all of my reports that use date criteria.

 

[KSReynolds]

I figured it out. I made the data selection form with an invisible text box to house the name of the report. When the Select Date form is activated, I have the value of the report name written to that text box. Then when the end date is entered, I have the vba code open up that report name in the invisible box.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSelectDateCriteria"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![frmSelectDateCriteria].txtReportName = "rptPreventiveReport"

Dim stDocName As String
stDocName = Me.txtReportName
DoCmd.OpenReport stDocName, acPreview

 

[pr2-eugin]

Well I have another option.. Not sure if you are interested, but here it is.. All you have to do is, to this Form add a ComboBox that will have the list of all Reports/Queries that will be using the similar Date criteria and then you can choose which Report to Run...

It will have the Date Criteria as you want it and also you give a flexibility to run which report you want instead of Creating several Buttons on the Form.

How does this sound?

 

[pr2-eugin]

I figured it out. I made the data selection form with an invisible text box to house the name of the report. When the Select Date form is activated, I have the value of the report name written to that text box. Then when the end date is entered, I have the vba code open up that report name in the invisible box.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSelectDateCriteria"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![frmSelectDateCriteria].txtReportName = "rptPreventiveReport"

Dim stDocName As String
stDocName = Me.txtReportName
DoCmd.OpenReport stDocName, acPreview

HUH ??!!??

 

[KSReynolds]

I didn't want to have to make an individual selection form for each report. This passes the name of the report to the Select Date form and then opens up that report name that has been passed to it.

 

[pr2-eugin]

Okay got it.. Did you look at the other approach I gave in Post#9?