Apply a filter to a Union Query

[jmarcrum]

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 (qryRadioDataUn ion) 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.

Private Sub cmdSearch_Click()
Dim GCriteria As String
 
    'Check for blank criteria
    If IsNull(chkExtraRadio) And IsNull(cmbRadioType) And IsNull(txtName) And IsNull(txtRadioNum) And IsNull(cmbContractor) And IsNull(cmbSIMCap) Then
        MsgBox "You must enter search criteria..."
        Exit Sub
    End If
 
        'Generate Radio Type search criteria
        If Not IsNull(Me.cmbRadioType) Then
            If GCriteria = "" Then
                GCriteria = "tblExtraRadios.RadioTypeID = " & Me.cmbRadioType.Value
            Else
                GCriteria = GCriteria & " AND tblExtraRadios.RadioTypeID = " & Me.txtRadioNum.Value & ""
            End If
        End If
 
        'Generate Name search criteria
        If Not IsNull(txtName) Then
            If GCriteria = "" Then
                GCriteria = "(((tblExtraRadios.Name) Like " & """" & "*" & Me.txtName.Value & "*" & """" & "))"
            Else
                GCriteria = GCriteria & " AND tblExtraRadios.Name = '" & Me.txtName.Value & "'"
            End If
        End If
 
        'Generate Radio Number search criteria
        If Not IsNull(txtRadioNum) Then
            If GCriteria = "" Then
                GCriteria = "(((tblExtraRadios.ExtraRadioNum) Like " & """" & "*" & Me.txtRadioNum.Value & "*" & """" & "))"
            Else
                GCriteria = GCriteria & " AND tblExtraRadios.ExtraRadioNum = " & Me.txtRadioNum.Value & ""
            End If
        End If
 
        'Generate SIM Capacity search criteria
        If Not IsNull(Me.cmbSIMCap) Then
            GCriteria = "tblExtraRadios.SIMCapacityID = " & Me.cmbSIMCap.Value
        End If
 
        'Generate Contractor search criteria
        If Not IsNull(Me.cmbContractor) Then
            If GCriteria = "" Then
                GCriteria = "tblCrews.Company = " & Me.cmbContractor.Value
            Else
                GCriteria = GCriteria & " AND tblCrews.Company = " & Me.cmbContractor.Value & ""
            End If
        End If
 
        'Generate Extra Radio for True search criteria
        If (Me.chkExtraRadio) = True Then
            If GCriteria = "" Then
                GCriteria = "tblExtraRadios.ExtraRadio = " & Me.chkExtraRadio.Value
            Else
                GCriteria = GCriteria & " AND tblExtraRadios.ExtraRadio = " & Me.chkExtraRadio.Value & ""
            End If
        End If
 
        'Generate Extra Radio for False search criteria
        If (Me.chkExtraRadio) = False Then
            If GCriteria = "" Then
                GCriteria = "tblCrew.ExtraRadio = " & Me.chkExtraRadio.Value
            Else
                GCriteria = GCriteria & " AND tblCrew.ExtraRadio = " & Me.chkExtraRadio.Value & ""
            End If
        End If
 
        'Filter frmRadioDataAll based on search criteria
        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" & _
                                                                "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" & _
                                                                "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;"
        Forms!frmRadioData.Caption = "Search Results"
End Sub

Thanks so much!

[MMcCarthy]

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.

[jmarcrum]

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 qryRadioDataUni onFINAL. Now can I just filter the fields in that query and it work? Like this?

Private Sub cmdSearch_Click()
Dim GCriteria As String
 
    'Check for blank criteria
    If IsNull(cmbRadioType) And IsNull(txtName) And IsNull(txtRadioNum) And IsNull(cmbContractor) And IsNull(cmbSIMCap) Then
        MsgBox "You must enter search criteria..."
        Exit Sub
    End If
 
        'Generate Radio Type search criteria
        If Not IsNull(cmbRadioType) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioType = " & Me.cmbRadioType.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate Name search criteria
        If Not IsNull(txtName) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "Name = " & Me.txtName.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate Radio Number search criteria
        If Not IsNull(txtRadioNum) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioNum = " & Me.txtRadioNum.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate SIM Capacity search criteria
        If Not IsNull(cmbSIMCap) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "SIMCapacityType = " & Me.cmbSIMCap.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate Contractor search criteria
        If Not IsNull(cmbContractor) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "ContractorName = " & Me.cmbContractor.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Filter frmRadioDataAll based on search criteria
        Forms!frmRadioData!frmRadioDataAll.Form.RecordSource = qryRadioDataUnion
 
        'Rename the form
        Forms!frmRadioData.Caption = "Search Results"
End Sub

[MMcCarthy]

OK, so I created another query and saved it as qryRadioDataUni onFINAL. Now can I just filter the fields in that query and it work? Like this?

Private Sub cmdSearch_Click()
Dim GCriteria As String
 
    'Check for blank criteria
    If IsNull(cmbRadioType) And IsNull(txtName) And IsNull(txtRadioNum) And IsNull(cmbContractor) And IsNull(cmbSIMCap) Then
        MsgBox "You must enter search criteria..."
        Exit Sub
    End If
 
        'Generate Radio Type search criteria
        If Not IsNull(cmbRadioType) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioType = " & Me.cmbRadioType.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate Name search criteria
        If Not IsNull(txtName) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "Name = " & Me.txtName.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate Radio Number search criteria
        If Not IsNull(txtRadioNum) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "RadioNum = " & Me.txtRadioNum.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate SIM Capacity search criteria
        If Not IsNull(cmbSIMCap) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "SIMCapacityType = " & Me.cmbSIMCap.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Generate Contractor search criteria
        If Not IsNull(cmbContractor) Then
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = "ContractorName = " & Me.cmbContractor.Value
            Forms!frmRadioData!frmRadioDataAll.Form.Filter = True
        End If
 
        'Filter frmRadioDataAll based on search criteria
        Forms!frmRadioData!frmRadioDataAll.Form.RecordSource = qryRadioDataUnion
 
        'Rename the form
        Forms!frmRadioData.Caption = "Search Results"
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.

[jmarcrum]

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.

[MMcCarthy]

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 qryRadioDataUni onFINAL 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.

[jmarcrum]

So you created a new query qryRadioDataUni onFINAL 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.