473,320 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Apply a filter to a Union Query

105 100+
Hi!

I have created a Union Query in ACCESS 2003, that combines 130 records from one query (Extra Foreman Radios) and 250 records from another query (Forman Main Radios). I have created a continuous form and used the Union query (qryRadioDataUnion) as my record source. My question is...how would i apply a filter to that query record source to allow the user to limit the search results down to an individual contractor, radio type, SIM card capacity, etc.

The problem is....I'm pullin from multiple tables here and I don't know which one to reference. Here's my code for the criteria on the continuous form. This occurs after the user selects their search criteria and then clicks the search button.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim GCriteria As String
  3.  
  4.     'Check for blank criteria
  5.     If IsNull(chkExtraRadio) And IsNull(cmbRadioType) And IsNull(txtName) And IsNull(txtRadioNum) And IsNull(cmbContractor) And IsNull(cmbSIMCap) Then
  6.         MsgBox "You must enter search criteria..."
  7.         Exit Sub
  8.     End If
  9.  
  10.         'Generate Radio Type search criteria
  11.         If Not IsNull(Me.cmbRadioType) Then
  12.             If GCriteria = "" Then
  13.                 GCriteria = "tblExtraRadios.RadioTypeID = " & Me.cmbRadioType.Value
  14.             Else
  15.                 GCriteria = GCriteria & " AND tblExtraRadios.RadioTypeID = " & Me.txtRadioNum.Value & ""
  16.             End If
  17.         End If
  18.  
  19.         'Generate Name search criteria
  20.         If Not IsNull(txtName) Then
  21.             If GCriteria = "" Then
  22.                 GCriteria = "(((tblExtraRadios.Name) Like " & """" & "*" & Me.txtName.Value & "*" & """" & "))"
  23.             Else
  24.                 GCriteria = GCriteria & " AND tblExtraRadios.Name = '" & Me.txtName.Value & "'"
  25.             End If
  26.         End If
  27.  
  28.         'Generate Radio Number search criteria
  29.         If Not IsNull(txtRadioNum) Then
  30.             If GCriteria = "" Then
  31.                 GCriteria = "(((tblExtraRadios.ExtraRadioNum) Like " & """" & "*" & Me.txtRadioNum.Value & "*" & """" & "))"
  32.             Else
  33.                 GCriteria = GCriteria & " AND tblExtraRadios.ExtraRadioNum = " & Me.txtRadioNum.Value & ""
  34.             End If
  35.         End If
  36.  
  37.         'Generate SIM Capacity search criteria
  38.         If Not IsNull(Me.cmbSIMCap) Then
  39.             GCriteria = "tblExtraRadios.SIMCapacityID = " & Me.cmbSIMCap.Value
  40.         End If
  41.  
  42.         'Generate Contractor search criteria
  43.         If Not IsNull(Me.cmbContractor) Then
  44.             If GCriteria = "" Then
  45.                 GCriteria = "tblCrews.Company = " & Me.cmbContractor.Value
  46.             Else
  47.                 GCriteria = GCriteria & " AND tblCrews.Company = " & Me.cmbContractor.Value & ""
  48.             End If
  49.         End If
  50.  
  51.         'Generate Extra Radio for True search criteria
  52.         If (Me.chkExtraRadio) = True Then
  53.             If GCriteria = "" Then
  54.                 GCriteria = "tblExtraRadios.ExtraRadio = " & Me.chkExtraRadio.Value
  55.             Else
  56.                 GCriteria = GCriteria & " AND tblExtraRadios.ExtraRadio = " & Me.chkExtraRadio.Value & ""
  57.             End If
  58.         End If
  59.  
  60.         'Generate Extra Radio for False search criteria
  61.         If (Me.chkExtraRadio) = False Then
  62.             If GCriteria = "" Then
  63.                 GCriteria = "tblCrew.ExtraRadio = " & Me.chkExtraRadio.Value
  64.             Else
  65.                 GCriteria = GCriteria & " AND tblCrew.ExtraRadio = " & Me.chkExtraRadio.Value & ""
  66.             End If
  67.         End If
  68.  
  69.         'Filter frmRadioDataAll based on search criteria
  70.         Forms!frmRadioData!frmRadioDataAll.Form.RecordSource = "SELECT DISTINCT tblExtraRadios.Name, tblExtraRadios.ExtraRadioNum AS RadioNum, tblContractor.ContractorName, tblExtraRadios.ExtraPhoneNumber AS PhoneNumber, tblRadioType.RadioType, tblSIMCapacity.SIMCapacityType, tblExtraRadios.RadioActivatedOn, tblExtraRadios.RadioChangedOn, tblExtraRadios.RadioDeactivatedOn, tblExtraRadios.Active AS RadioActive, tblExtraRadios.Notes AS RadioNotes, tblExtraRadios.ExtraRadio" & _
  71.                                                                 "FROM tblSIMCapacity RIGHT JOIN (tblRadioType RIGHT JOIN (tblContractor RIGHT JOIN (tblCrews RIGHT JOIN tblExtraRadios ON tblCrews.CrewID=tblExtraRadios.CrewID) ON tblContractor.ContractorID=tblCrews.Company) ON tblRadioType.RadioTypeID=tblExtraRadios.RadioTypeID) ON tblSIMCapacity.SIMCapacityID=tblExtraRadios.SIMCapacityID ORDER BY tblExtraRadios.Name UNION SELECT  tblCrews.ForemanFirstName & ' ' & tblCrews.ForemanLastName AS Name, tblCrews.RadioNum, tblContractor.ContractorName, tblCrews.PhoneNumber, tblRadioType.RadioType, tblSIMCapacity.SIMCapacityType, tblCrews.RadioActivatedOn, tblCrews.RadioChangedOn, tblCrews.RadioDeactivatedOn, tblCrews.RadioActive, tblCrews.RadioNotes, tblCrews.ExtraRadio" & _
  72.                                                                 "FROM tblSIMCapacity RIGHT JOIN (tblRadioType RIGHT JOIN (tblContractor RIGHT JOIN tblCrews ON tblContractor.ContractorID = tblCrews.Company) ON tblRadioType.RadioTypeID = tblCrews.RadioTypeID) ON tblSIMCapacity.SIMCapacityID = tblCrews.SIMCapacityID;"
  73.         Forms!frmRadioData.Caption = "Search Results"
  74. End Sub
Thanks so much!
Jan 30 '08 #1
6 6980
MMcCarthy
14,534 Expert Mod 8TB
You can't apply a filter on the overall query unless you save it and build another query on it. You would have to set criteria on each query within the union.
Feb 5 '08 #2
jmarcrum
105 100+
You can't apply a filter on the overall query unless you save it and build another query on it. You would have to set criteria on each query within the union.
OK, so I created another query and saved it as qryRadioDataUnionFINAL. Now can I just filter the fields in that query and it work? Like this?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim GCriteria As String
  3.  
  4.     'Check for blank criteria
  5.     If IsNull(cmbRadioType) And IsNull(txtName) And IsNull(txtRadioNum) And IsNull(cmbContractor) And IsNull(cmbSIMCap) Then
  6.         MsgBox "You must enter search criteria..."
  7.         Exit Sub
  8.     End If
  9.  
  10.         'Generate Radio Type search criteria
  11.         If Not IsNull(cmbRadioType) Then
  12.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioType = " & Me.cmbRadioType.Value
  13.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  14.         End If
  15.  
  16.         'Generate Name search criteria
  17.         If Not IsNull(txtName) Then
  18.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "Name = " & Me.txtName.Value
  19.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  20.         End If
  21.  
  22.         'Generate Radio Number search criteria
  23.         If Not IsNull(txtRadioNum) Then
  24.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioNum = " & Me.txtRadioNum.Value
  25.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  26.         End If
  27.  
  28.         'Generate SIM Capacity search criteria
  29.         If Not IsNull(cmbSIMCap) Then
  30.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "SIMCapacityType = " & Me.cmbSIMCap.Value
  31.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  32.         End If
  33.  
  34.         'Generate Contractor search criteria
  35.         If Not IsNull(cmbContractor) Then
  36.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "ContractorName = " & Me.cmbContractor.Value
  37.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  38.         End If
  39.  
  40.         'Filter frmRadioDataAll based on search criteria
  41.         Forms!frmRadioData!frmRadioDataAll.Form.RecordSource = qryRadioDataUnion
  42.  
  43.         'Rename the form
  44.         Forms!frmRadioData.Caption = "Search Results"
  45. End Sub
Feb 6 '08 #3
MMcCarthy
14,534 Expert Mod 8TB
OK, so I created another query and saved it as qryRadioDataUnionFINAL. Now can I just filter the fields in that query and it work? Like this?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. Dim GCriteria As String
  3.  
  4.     'Check for blank criteria
  5.     If IsNull(cmbRadioType) And IsNull(txtName) And IsNull(txtRadioNum) And IsNull(cmbContractor) And IsNull(cmbSIMCap) Then
  6.         MsgBox "You must enter search criteria..."
  7.         Exit Sub
  8.     End If
  9.  
  10.         'Generate Radio Type search criteria
  11.         If Not IsNull(cmbRadioType) Then
  12.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioType = " & Me.cmbRadioType.Value
  13.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  14.         End If
  15.  
  16.         'Generate Name search criteria
  17.         If Not IsNull(txtName) Then
  18.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "Name = " & Me.txtName.Value
  19.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  20.         End If
  21.  
  22.         'Generate Radio Number search criteria
  23.         If Not IsNull(txtRadioNum) Then
  24.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioNum = " & Me.txtRadioNum.Value
  25.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  26.         End If
  27.  
  28.         'Generate SIM Capacity search criteria
  29.         If Not IsNull(cmbSIMCap) Then
  30.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "SIMCapacityType = " & Me.cmbSIMCap.Value
  31.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  32.         End If
  33.  
  34.         'Generate Contractor search criteria
  35.         If Not IsNull(cmbContractor) Then
  36.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = "ContractorName = " & Me.cmbContractor.Value
  37.             Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
  38.         End If
  39.  
  40.         'Filter frmRadioDataAll based on search criteria
  41.         Forms!frmRadioData!frmRadioDataAll.Form.RecordSource = qryRadioDataUnion
  42.  
  43.         'Rename the form
  44.         Forms!frmRadioData.Caption = "Search Results"
  45. End Sub
Have you tried it? I don't know if it will work with your data, you have to try it out and see. I think it will but I can't see your database.
Feb 6 '08 #4
jmarcrum
105 100+
Have you tried it? I don't know if it will work with your data, you have to try it out and see. I think it will but I can't see your database.
Well, i tried it, but it displays in each text box within the Detail part of the continuous form #Name, #Name, #Name, #Name, #Name, #Name, #Name, ....

I'm not sure why it's doing that.
Feb 6 '08 #5
MMcCarthy
14,534 Expert Mod 8TB
Well, i tried it, but it displays in each text box within the Detail part of the continuous form #Name, #Name, #Name, #Name, #Name, #Name, #Name, ....

I'm not sure why it's doing that.
So you created a new query qryRadioDataUnionFINAL which essentially is a select query which selects all fields from the union query?
You have set that query as the record source of your form?

If that doesn't work then you just can't filter on a union query. The only other option is to trigger a make table query and create a new table based on the union query results and use that as the record source of the form.
Feb 6 '08 #6
jmarcrum
105 100+
So you created a new query qryRadioDataUnionFINAL which essentially is a select query which selects all fields from the union query?
You have set that query as the record source of your form?
Yeah that's what I'm finding out. If you want to filter a union query...you gotta do it WITHIN the SQL statement. You can't do it with user input. That other option you suggested might work though. I wish I had time to try, my boss is pressin' time on me.

But what I ended up doing was separating the tables and just running criteria checks on two separate forms. It'll work just as well, may take some more time though...haha.
Feb 6 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: nick_faye | last post by:
hi guys, hope somebody can assist me. i have two ms access database. i have to copy the entries in database1 to my database2. however, i have to copy entries from database1 that does not...
16
by: Nhmiller | last post by:
I already have a report designed under Reports. When I use filtering in Forms or Tables, I see no way to select that filtered list when I am in Reports, and there appears to be no way to do the...
3
by: Stewart | last post by:
Hi all! My (relatively small) database holds data on staff members and the projects (services) that they are assigned to. In my form frmStaff, I have a list of staff members - it is a...
2
by: Lenin Torres | last post by:
Hi everybody I have an Union Query that works fine. I used this query as the RecordSource for a Form. That Form is used as a subform in another form. Everything works fine, except for the "Filter...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
6
by: cwby1966 | last post by:
This code worked great in about 8 other places now i am tryng to use in an existing database and when i run the query it gies me a syntex error and then it makes itself into a Union query andy Idea...
1
by: mattscho | last post by:
Re: Filter By From, Apply Filter, Remove Filter Buttons in a Form. -------------------------------------------------------------------------------- Hi All, Trying to create a set of 3 buttons in...
10
by: dbdb | last post by:
Hi, i create a chart in ms access based on my query, then i want my chart when is it open is only show value based on my criteria. i'll try to used it in the properties apply filter using the...
5
by: phill86 | last post by:
Hi, I have a form that I have applied a filter to by using combo boxes which works fine. Is there a way to apply that filter to the forms underlying query Here is the code that I use to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.