Solved

Same Date Range Parameter for 2 fields

Posted on 2006-10-25
14
259 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!
0
Comment
Question by:rjjcomp
14 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17807581
>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
0
 

Author Comment

by:rjjcomp
ID: 17807628
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.
0
 

Author Comment

by:rjjcomp
ID: 17807633
sorry--NOT sure
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 17807705
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)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17808127
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)
0
 
LVL 8

Expert Comment

by:infolurk
ID: 17808461
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
0
 

Author Comment

by:rjjcomp
ID: 17813659
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!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17813721
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.
0
 

Author Comment

by:rjjcomp
ID: 17814086
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...)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17814140
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"

0
 

Author Comment

by:rjjcomp
ID: 17814310
But shoudn't the form itself open, so a date range can be selected?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 17814367
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.
0
 

Author Comment

by:rjjcomp
ID: 17814809
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!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17814949
Thanks! :-)
I'm glad I could help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question