Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Same Date Range Parameter for 2 fields

Posted on 2006-10-25
14
Medium Priority
?
283 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 66

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
Technology Partners: 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 1000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

971 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