By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,128 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Apply a filter to a Union Query

100+
P: 105
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
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

100+
P: 105
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
Expert Mod 10K+
P: 14,534
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

100+
P: 105
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
Expert Mod 10K+
P: 14,534
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

100+
P: 105
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

Post your reply

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