We help IT Professionals succeed at work.

Same Date Range Parameter for 2 fields

rjjcomp
rjjcomp asked
on
Medium Priority
299 Views
Last Modified: 2012-06-22
I have a "Between" date range parameter for a query field named "Date of Service". How can I utilize these same dates as criteria for another field in the same query, without having the user prompted again for the 2 dates?
e.g., I want the records selected for the second field to be based on the same date range parameter as specified for the first, automatically.

I am working in the Select Query design view (I can do OK writing expressions, but not very comfortable writing SQL statements)

Thanks!
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>How can I utilize these same dates as criteria for another field in the same query, without having the user prompted again for the 2 dates?
The easiest way would be to have a user enter them on a form, then as long as that form stays open queries can refer to it like this...

SELECT blah1, blah2, blah3
FROM blah
WHERE blahStartDate > Forms![YourFormName]![YourStartDateTextBox] AND blahEndDate < Forms![YourFormName]![YourEndDateTextBox]

Make sure in that form you force the user to enter valid dates in both text boxes before kicking off the queries.

Hope this helps.
-Jim

Author

Commented:
Thanks, but the query is being used for a report, and I'm sure how to enter in the criteria for the second date field.

Author

Commented:
sorry--NOT sure
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
You can use the query designer to refer to the dates on the form (the form needs to be open when you run the query) by right-clicking the criteria field (do this for both fields that need to meet the criteria), selecting  build  -> Forms -> Loaded Forms and selecting the appropriate textbox name.

The resultant SQL will look something like this:

Select f1,f2, etc...
From YourTable
Where (F1 Between Forms!YourForm!YourTextbox1 AND Forms!YourForm!YourTextbox1) AND (F2 Between Forms!YourForm!YourTextbox1 AND Forms!YourForm!YourTextbox1)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Copy/paste problems.  With any luck your SQL will look more like this:

Select f1,f2, etc...
From YourTable
Where (F1 Between Forms!YourForm!YourTextbox1 AND Forms!YourForm!YourTextbox2) AND (F2 Between Forms!YourForm!YourTextbox1 AND Forms!YourForm!YourTextbox2)

Commented:
Basically, What everyone is saying is instead of using a parameter query, put from and to date input boxes on a form, prompt the user for input and then call the report from a button on the form. With a parameter query you will have to get the dates input twice. With a form you can get the query to look at the input boxes for the dates, as many times as you like.

cheers
Steve

Author

Commented:
OK-I have the form with the input boxes created, and the query fields modifed to pull from the form.  How do I modify the report, so that when the query is executed it opens the form.  I looked at the Northwind database "Sales By Year" form, query, and report, but my report returns error message, rather than open the form.  It appears (at least in the Northwind example) that some event function code in VB is required in the report to do this, which I'm not familiar with at all.  Help please!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Set the recordsource of your report to the query.  Then open the report through a command button on the form that  has the date textboxes:

cmdOpenReport_Click
    Docmd.openreport "rptYourReport", acpreview
End Sub

The report will pull the criteria from the form, which remains open in the background.

Author

Commented:
That's just what I have. I must be missing something, because when I run the query from the design window, or try to run the report itself, no parameter prompts come up at all, and no records selected.  I've double checked the Form and Field names in the query, the command button on the form and even created a new AutoReport from the query--same results.  It seems like the query isn't recognizing the form parameters.  When I had the parameters defined in the query design window instead of the form, the prompts came up and all was well.  (Other than the original issue anyway...)
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
You won't get the parameter prompts since the criteria is being read directly from the form.  Double check that you actually have data for that date range.  If the data looks okay, come back and copy/paste the SQL to your query here:

- Open your query in design view
- right click in the query window and select "SQL View"

Author

Commented:
But shoudn't the form itself open, so a date range can be selected?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Yes.  Where are you currently (before this thread) opening the report?

If the report is directly being opened from another form, use the command button that was previously opening the report to open this "filter" form instead.  Then use a command button on the new form to open the report.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
MBIZUP:
Thanks a million!  I finally have it working now.  I don't know why I didn't realize the FORM should be accessd first from the switchboard, fill in the dates, and THEN execute the report--DUUUHH!

I will quickly accept your solution and get you the well earned points!

Keep your eyes open--I have more to this project that I'm sure I will need assistance on!
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Thanks! :-)
I'm glad I could help.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.