Avatar of Michael Katz
Michael Katz
Flag for United States of America asked on

MS Access 2007 and Parameters with Union queries

Hi Experts,

I have a union query that has 3 fields a Text field, Number Field and a Date Field. I need to have a parameter on the Date Field. I thought I would put a Where clause in each part of the union example

Select * from qry1
Where Date between [enter beg date] and [enter end date]
union
Select * from qry2
where date between [enter beg date] and [enter end date] and part = 'M'

I then want to have this Create a local table..

problem is i want just one set of prompts not 2...

I realize this is a very simple question...Any ideas would be really appreciated
Microsoft Access

Avatar of undefined
Last Comment
Michael Katz

8/22/2022 - Mon
Jeffrey Coachman

One approach is to not collect he data from user prompts.
(users can/will type invalid dates, leave things blank, ...etc)

Instead, create a form with two date boxes.

Then make the source for your query:
Select * from qry1
Where Date between Forms!YourForm!txtBegDate  AND Forms!YourForm!txtEndDate
union
Select * from qry2
where date between Forms!YourForm!BegDate  AND Forms!YourForm!EndDate AND and part = 'M'

JeffCoachman
Jeffrey Coachman

...There may be a simpler way to do this, so let's see what other Experts may post.

;-)

Jeff
Dale Fye

No points please.  I agree with Jeff; by far the best solution is to use a form to enter those values.  

That way you can check to ensure the values are valid dates, that both values are entered (or take steps to account for blanks), and ensure that the End Date is actually after the BeginDate.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

fyed,
If you want to post enhancements (validations) to my post, please feel free...
There is nothing "groundbreaking" in my post there...

;-)

Jeff
Dale Fye

Another way to address this would be with a couple of functions (fnStartDate, fnEndDate).  The syntax for this would be something like:

Select * from qry1
Where Date between fnStartDate(Null, True) AND fnEndDate(Null, True)
union
Select * from qry2
where date between fnStartDate() AND fnEndDate() AND and part = 'M'

And the functions fnStartDate() and fnEndDate() would look like:
Public Function fnStartDate(Optional varDate As Variant = Null, Optional Reset As Boolean) As Date

    Static myDate As Variant
    Dim strDate As String
    
    If Reset = True Then myDate = Null
    
    Do
        
        If IsNull(varDate) = False Then
            strDate = varDate
        ElseIf IsNull(myDate) = False Then
            strDate = Format(myDate, "mm/dd/yy")
        Else
            strDate = InputBox("Enter start date (mm/dd/yy)")
        End If
        
        'you could insert multiple tests here, if needed
        If IsDate(strDate) = False Then
            MsgBox "Enter a valid date!"
        Else
            myDate = CDate(strDate)
        End If
        
    Loop While IsNull(myDate)
    
    fnStartDate = myDate
        
End Function

Open in new window

and
Public Function fnEndDate(Optional varDate As Variant = Null, Optional Reset As Boolean) As Date

    Static myDate As Variant
    Dim strDate As String
    
    If Reset = True Then myDate = Null
    
    Do
        
        If IsNull(varDate) = False Then
            strDate = varDate
        ElseIf IsNull(myDate) = False Then
            strDate = Format(myDate, "mm/dd/yy")
        Else
            strDate = InputBox("Enter end date (mm/dd/yy)")
        End If
        
        'you could insert multiple tests here, if needed
        If IsDate(strDate) = False Then
            MsgBox "Enter a valid date!"
        Else
            myDate = CDate(strDate)
        End If
        
    Loop While IsNull(myDate)
    
    fnEndDate = myDate
        
End Function

Open in new window

This code is designed to retain the values of myDate between calls to the function, so it will ask you for the Start date once, and the End date once using this syntax.  You can also pass a value to each of the functions which would be useful if you enter a value in control, you could use the AfterUpdate of that control to set the value of the function, so that the query would not even ask you for the value.  Notice that in the above SQL string, I passed the Reset value of True to each of the functions to force the function to ask you for a date, but in the second part of the query, I left the optional parameters out, so that it will use the values that were already entered.
Michael Katz

ASKER
Hi boag2000 I tried what you posted...


SELECT * from qrySumQtySoldByDate
Where InvoiceDate between Forms!DateForm!txtBegDate and Forms!DateForm!txtEndDate
UNION Select * from qryNonSales
Where Date between Forms!DateForm!txtBegDate and Forms!DateForm!txtEndDate

and i am getting 2 sets of prompts...each asking for a BegDate and EndDate...

like i said i am very new to this..
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jerryb30

SELECT *
FROM qrySumQtySoldByDate
WHERE invoiceDate between Forms![DateForm]![txtBegDate].[value] And [Forms]![DateForm]![txtEndDate].[value]
union all
SELECT *
FROM qryNonSales
WHERE Date between Forms![DateForm]![txtBegDate].[value] And [Forms]![DateForm]![txtEndDate].[value]
jerryb30

Oh, if you are still being prompted, post the SQL of your two queries.
Dale Fye

Another way to go about this is to create the unions as a subquery:

SELECT Temp.*
FROM (
SELECT [TextField], [NumberField], InvoiceDate as DateField FROM qrySumQtySoldByDate
UNION all
SELECT [TextField], [NumberField], [DateFieldName] FROM qryNonSales
) as Temp
WHERE Temp.[DateField] BETWEEN cDate([Start Date]) AND cDate([End Date])

Notice that in the first part of the Union query, I aliased the [InvoiceDate] field as [DateField].  Because this is a Union query, The field names that will be used for the three fields are those that come from the first SELECT statement.  Therefore, when I build the WHERE clause for the Temp table, I used [DateField] as the field name.  Also note that I used the cDate() function to convert the parameter prompts for the [Start Date] and [End Date].  While I prefer to use form textboxes, this is generally acceptable unless you don't enter a valid date, then the query will probably generate an error.

If you are still being prompted, after reverting to using the Forms!DateForm.txtBegDate syntax, then check to make sure that you didn't actually declare your parameters.  If you did, the SQL will start with a line that looks something like:

Parameters [Start Date] datetime, [End Date] datetime;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Hamed Nasr

Try:

PARAMETERS [enter beg date] DateTime, [enter end date] DateTime;
Select * from qry1
Where Date between [enter beg date] and [enter end date]
union
Select * from qry2
where date between [enter beg date] and [enter end date] and part = 'M'

Open in new window

Chris B

Shouldn't be getting prompts if your form is set up correctly (the field names correspond exactly with the criteria in your query) and the form is open. Best to run the query from a button on the form. The criteria boxes can be unbound, but if you want to run the query with the form closed, you will need to bind it to a table with the two dates stored in it, then reference the table as the query criteria. You might do it this way if you were using a calendar control to enter the dates.

Chris B
Richard Daneke

I haven't walked through the code yet, but if you are going to use VBA, what about a function that uses the two dates and then executes the Union statement?

Along the lines of:

Function MyUnion(StartDate, EndDate)

   call union SQL with function variables

End Function
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Michael Katz

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael Katz

ASKER
Hi everyone

My form is soooo basic... but  why wont it work

I tried using the

Between Forms![DateForm]![txtBegDate] and Forms![DateForm]![txtEndDate]

in each of the subqueries.. Same thing happens

2 sets of Date prompts...All i want is 1 set of Date Prompts.. ShortForm no time..
Chris B

Can you post your db with a small data sample so we can see what is going on?

Just do a compact on an empty copy, add some dummy data, zip and post.

Chris B
Hamed Nasr

Try this sample database.
Check tables: a(f1, f2, dt) date yy/mm/yyyy
f1      f2      dt
1      1      01/01/2013
2      1      03/01/2013
1      2      02/01/2013
2      2      04/01/2013
4      3      05/01/2013

 and b
f1      f2      dt
5      2      01/01/2013
7      4      03/01/2013
9      6      02/01/2013
10      8      04/01/2013
11      10      05/01/2013

Run query: a_query

PARAMETERS [enter beg date] DateTime, [enter End date] DateTime;
SELECT a.f1, a.f2, a.dt
FROM a
WHERE (((a.dt) Between [enter beg date] And [enter End date]));
UNION SELECT b.f1, b.f2, b.dt
FROM b
WHERE (((b.dt) Between [enter beg date] And [enter End date]));

Output sample: Between 1/1/2013 And 2/1/2013
f1      f2      dt
1      1      01/01/2013
1      2      02/01/2013
5      2      01/01/2013
9      6      02/01/2013
betwwn-dates-Q-27979638.mdb
Your help has saved me hundreds of hours of internet surfing.
fblack61
Dale Fye

What do you mean "it doesn't work"?

When you use the wildcard (*) to select all of the fields, it will take all of the fields from the table (or query in this case), in the same order that they are in.

But those fields (and datatypes) must line up exactly in the two separate queries you are using.  The way I check this is to open up each of the queries so that I can see them on screen, one above the other, and I check to make sure that each field matches the one above it.  Field names are not important, just that the data types match across every field in each of those two queries.
Dale Fye

BTW, have you checked the SQL of the two queries themselves to determine whether they have parameters embeded in them?  If they do, then define the same parameters in those two queries (as in your union query).
Dale Fye

The various experts spent quite a bit of time assisting you with this.  If they:

"Guided me in the right direction"

don't you think you should award some points to the one or more who did the guiding?

Just one more question, this query isn't being run as the source of a report is it?  If so, did you check the grouping to make sure you had not changed the name of a grouping column?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Michael Katz

ASKER
Guided me in the right direction