MS Access 2007 and Parameters with Union queries

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

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

;-)

Jeff

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.

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

;-)

Jeff

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

Select all 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

Select all 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.

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]

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

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;

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'

Select all Open in new window

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

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

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

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

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.

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).

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?