Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: How to filer a Union Query

    Evening All

    I have created a union query following instruction which works correct, but have no knowlege of SQL and want to be able to filter the results from the query from a form. Which has two text boxes Start Date and End Date. and a button which I persume would just use the docmd to open a form and the filtering is completed within SQL

    Can any one point me in the right direction on how to complete this task

    Thank you

  2. #2
    Join Date
    Feb 2013
    Posts
    99
    This is the cirrent SQL

    SELECT [Fire Alarm Report].Date AS [Date], [Fire Alarm Report].[Time Fire Alarm Reported] AS [Time Reported], [Fire Alarm Report].[Time of Arrival], [Fire Alarm Report].Location, [Fire Alarm Report].[Attended By], [Fire Alarm Report].[Incident Type] AS [Incident Description], [Fire Alarm Report].[Cause of Activation] AS [Detailed Description]
    FROM [Fire Alarm Report]
    Union All
    SELECT [Incident Report].[Date of Incident], [Incident Report].[Time Incident Reported], [Incident Report].[Time of Arrival], [Incident Report].Location, [Incident Report].[Attended By], [Incident Report].[Type of Incident], [Incident Report].[Incident Summary]
    FROM [Incident Report]
    Union All
    SELECT Lockouts.Date, Lockouts.[Lockout Reported At], Lockouts.[Time of Arrival], Lockouts.Location, Lockouts.[Attended By], Lockouts.[Incident Type], Lockouts.[Room Number]
    FROM Lockouts
    UNION ALL SELECT [Intruder Alarm Report].Date, [Intruder Alarm Report].[Time Intruder Alarm Reported], [Intruder Alarm Report].[Time of Arrival], [Intruder Alarm Report].Location, [Intruder Alarm Report].[Attended By], [Intruder Alarm Report].[Incident Type], [Intruder Alarm Report].[Cause of Activation]
    FROM [Intruder Alarm Report]
    ORDER BY Date;

  3. #3
    Join Date
    Nov 2011
    Posts
    11
    Well you could just wrap the entire union query in a select then do a typical where useing your text boxes to do a like with if not useing a specific list of values.

    I personally would do the above this way for ease not efficency(Not knowing how your skill is.)
    1. Select * from SELECT [Fire Alarm Report].Date AS [Date], [Fire Alarm Report].[Time Fire Alarm Reported] AS [Time Reported], [Fire Alarm Report].[Time of Arrival], [Fire Alarm Report].Location, [Fire Alarm Report].[Attended By], [Fire Alarm Report].[Incident Type] AS [Incident Description], [Fire Alarm Report].[Cause of Activation] AS [Detailed Description]
    FROM [Fire Alarm Report]
    Union All
    SELECT [Incident Report].[Date of Incident], [Incident Report].[Time Incident Reported], [Incident Report].[Time of Arrival], [Incident Report].Location, [Incident Report].[Attended By], [Incident Report].[Type of Incident], [Incident Report].[Incident Summary]
    FROM [Incident Report]
    Union All
    SELECT Lockouts.Date, Lockouts.[Lockout Reported At], Lockouts.[Time of Arrival], Lockouts.Location, Lockouts.[Attended By], Lockouts.[Incident Type], Lockouts.[Room Number]
    FROM Lockouts
    UNION ALL SELECT [Intruder Alarm Report].Date, [Intruder Alarm Report].[Time Intruder Alarm Reported], [Intruder Alarm Report].[Time of Arrival], [Intruder Alarm Report].Location, [Intruder Alarm Report].[Attended By], [Intruder Alarm Report].[Incident Type], [Intruder Alarm Report].[Cause of Activation]
    FROM [Intruder Alarm Report]
    ORDER BY Date) as Data


    Then on a button when you click on it it will look at the data in the box and apply a filter by using the On Click Event.

    me.form.filter = "[Column to filter on] between '" & me.TextBox1.value & "'" and '" & me.TextBox2.value & "'"
    Me.form.filterOn = true

    This is just a quick awnser seeing no one got around to it yet. but its a start for you! Might have to change the single quotes and stuff only because it might be diffrent for dates. I am sure some of the more usual experts will be able to give you a better solution but this should work in the interim.
    Last edited by jblanch8; 07-22-13 at 16:40.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Alternatively, base a second form on the union query and use this method to open it:

    Open a second form to the record
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •