Solved

Same Date Range Parameter for 2 fields

Posted on 2006-10-25
14
237 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now