Solved

MS Access 2007 and Parameters with Union queries

Posted on 2012-12-28
21
511 Views
Last Modified: 2013-02-03
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
0
Comment
Question by:Mikeyman_01
  • 6
  • 4
  • 3
  • +4
21 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727256
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727265
...There may be a simpler way to do this, so let's see what other Experts may post.

;-)

Jeff
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38727287
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38727389
fyed,
If you want to post enhancements (validations) to my post, please feel free...
There is nothing "groundbreaking" in my post there...

;-)

Jeff
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38727584
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.
0
 

Author Comment

by:Mikeyman_01
ID: 38747555
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..
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38761255
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]
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38761266
Oh, if you are still being prompted, post the SQL of your two queries.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38761414
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;
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38761655
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

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 28

Expert Comment

by:burrcm
ID: 38762288
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
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 38762822
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
0
 

Accepted Solution

by:
Mikeyman_01 earned 0 total points
ID: 38829910
Hi everyone,

What in gods name am i missing??  I really don't understand...

I have tried..

PARAMETERS [enter beg date] Date, [enter end date] Date;

SELECT *
FROM qrySumQtySoldByDate
WHERE invoiceDate between [enter beg date] and [enter end date]
union all
SELECT * fROM qryNonSales
WHERE InvDate between [enter beg date] and [enter end date]

It would make sense that it would work.. but it doesnt..

WHAT THE HELL AM I DOING WRONG??  I JUST WANT ONE SET OF DATES...
0
 

Author Comment

by:Mikeyman_01
ID: 38829916
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..
0
 
LVL 28

Expert Comment

by:burrcm
ID: 38830010
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
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38830458
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38830643
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38830661
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).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38834815
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?
0
 

Author Closing Comment

by:Mikeyman_01
ID: 38848337
Guided me in the right direction
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dateadd 3 22
How calculate median 5 38
Access Delimiter for check box filter 3 14
Minus first query 1 0
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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 Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

9 Experts available now in Live!

Get 1:1 Help Now