Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Same Date Range Parameter for 2 fields

Posted on 2006-10-25
14
Medium Priority
?
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

715 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