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. - 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!
6 6980
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.
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? - 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
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? - 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.
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.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |