Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Apply a filter to a Union Query

Member
  
Join Date: Oct 2007
Posts: 102
#1: Jan 30 '08
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!

msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#2: Feb 5 '08

re: Apply a filter to a Union Query

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.
Member
  
Join Date: Oct 2007
Posts: 102
#3: Feb 6 '08

re: Apply a filter to a Union Query

Quote:

Originally Posted by msquared

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
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#4: Feb 6 '08

re: Apply a filter to a Union Query

Quote:

Originally Posted by jmarcrum

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.
Member
  
Join Date: Oct 2007
Posts: 102
#5: Feb 6 '08

re: Apply a filter to a Union Query

Quote:

Originally Posted by msquared

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.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,885
#6: Feb 6 '08

re: Apply a filter to a Union Query

Quote:

Originally Posted by jmarcrum

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.
Member
  
Join Date: Oct 2007
Posts: 102
#7: Feb 6 '08

re: Apply a filter to a Union Query

Quote:

Originally Posted by msquared

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

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,467 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved