Filter Report
- Thread starter brsawvel
- Start date
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
You can build a query to base your report off of, I find this the best way since a query is more easily edited afterwards.
In the query you would make the criteria point towards the particular combobox on the form that you wish to filter under.
Say I have a table with ID, Name, Department and make a query including all three of these values.
I have a form named frmSearch which contains a combobox cboDepartment which includes a list of all departments.
In the query under the criteria section I put:
=[Forms]![frmSearch]![cboDepartment]
Whichever department I select in the combobox and hit the report button, the query only includes the department selected and gets put into the report. So selecting "Finance" as the department would produce a report of everyone in the Finance Department.
Hope this helps.
In the query you would make the criteria point towards the particular combobox on the form that you wish to filter under.
Say I have a table with ID, Name, Department and make a query including all three of these values.
I have a form named frmSearch which contains a combobox cboDepartment which includes a list of all departments.
In the query under the criteria section I put:
=[Forms]![frmSearch]![cboDepartment]
Whichever department I select in the combobox and hit the report button, the query only includes the department selected and gets put into the report. So selecting "Finance" as the department would produce a report of everyone in the Finance Department.
Hope this helps.
I tried your recommendation, but I'm doing something wrong because it comes up with an error and then clicking on the report button sends all records to the report still.
I attached a scrubbed version of the db (without company data) to show you what I'm attempting to do.
The "Ticket Requests" button brings up all the tickets in the lower subform as listed and clicking on one of the records in the listbox displays that record in detail on the right subform.
The "T" button is supposed to bring up the filter form (which it does after it asks for a paramater for each field) and then clicking on the Report button after making my filter selections is supposed to bring up a filtered report (which comes up as a full report).
Can you see where I'm going wrong?
Please Help
I attached a scrubbed version of the db (without company data) to show you what I'm attempting to do.
The "Ticket Requests" button brings up all the tickets in the lower subform as listed and clicking on one of the records in the listbox displays that record in detail on the right subform.
The "T" button is supposed to bring up the filter form (which it does after it asks for a paramater for each field) and then clicking on the Report button after making my filter selections is supposed to bring up a filtered report (which comes up as a full report).
Can you see where I'm going wrong?
Please Help
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
I am not fully aware what you're trying to do here... but here is what I did.
I made a frmSearch that shows how you can filter your current report based on what "Status" was entered into the combobox.
I think the problem with yours might have been that your report was actually based off the table directly and not the query in which you were putting your filter code.
I made a frmSearch that shows how you can filter your current report based on what "Status" was entered into the combobox.
I think the problem with yours might have been that your report was actually based off the table directly and not the query in which you were putting your filter code.
I think I figured out the problem - to a point. I know that if I set the query to only filter by one field, then the filter works. If I try and set the query up to filter by many fields, then the filter doesn't work.
I'm guessing that's because I don't enter data in some of the fields to filter by.
Does that make sense?
Is there something I could make as default text in a box that tells the query to select all in that column? (i.e. *)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I did figure it out. The problem is that I have a search/report form with a number of text or combo boxes:
Country
Site
Status
Qty Ordered
But sometimes I just want to search just by country and sometimes I want to search just by site and status. So I would leave the other boxes blank. I'm guessing Access is performing such that when I leave the other boxes blank, it either doesn't see a match or it is confused so it doesn't send anything to the report.
So is there a way to set the textbox, combobox, or query so that if I don't enter search criteria into one of the boxes, Access automatically selects all in that column?
I'm guessing that's because I don't enter data in some of the fields to filter by.
Does that make sense?
Is there something I could make as default text in a box that tells the query to select all in that column? (i.e. *)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I did figure it out. The problem is that I have a search/report form with a number of text or combo boxes:
Country
Site
Status
Qty Ordered
But sometimes I just want to search just by country and sometimes I want to search just by site and status. So I would leave the other boxes blank. I'm guessing Access is performing such that when I leave the other boxes blank, it either doesn't see a match or it is confused so it doesn't send anything to the report.
So is there a way to set the textbox, combobox, or query so that if I don't enter search criteria into one of the boxes, Access automatically selects all in that column?
Last edited:
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
Ok I see what you are trying to do. You may be able to use If statements inside the query, however try using the following code on the button for the report, it generates a where condition based on what search criteria fields were entered. Don't forget that you will have to clear all of the criteria fields in your query that the report is based off of.
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtCountry]) Then
strWhere = ( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
End If
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtSite]) Then
strWhere = "( [Site] = [Forms]![frmSearch]![txtSite]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtStatus]) Then
strWhere = "( [Status] = [Forms]![frmSearch]![txtStatus]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtQty]) Then
strWhere = "( [Qty] = [Forms]![frmSearch]![txtQty]) AND " & strWhere
End If
'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5
'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "rptName"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End If
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
Dim stDocName As String
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtCountry]) Then
strWhere = ( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
End If
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtSite]) Then
strWhere = "( [Site] = [Forms]![frmSearch]![txtSite]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtStatus]) Then
strWhere = "( [Status] = [Forms]![frmSearch]![txtStatus]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition
If Not IsNull([Forms]![frmSearch]![txtQty]) Then
strWhere = "( [Qty] = [Forms]![frmSearch]![txtQty]) AND " & strWhere
End If
'This is used to cut off the " AND " at the end of the Where Condition
lngLen = Len(strWhere) - 5
'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "rptName"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End If
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
I tried the recommendation and wasn't successful. I noticed you said it may be possible to write if statements inside a query. That's what I thought too. I just don't know how that would appear. I'm still working to figure it out.
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
I will take your example database uploaded in this thread and put in the suggested code and reupload later today.
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
Hi, I'm at work and managed to just get the time to test the code myself, I missed one quotation mark in my code in this line:
strWhere = ( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
should read:
strWhere = "( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
However, I do have a small tiny sample database of this search working. You can input any number of the fields to filter out the data, give it a try!
strWhere = ( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
should read:
strWhere = "( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
However, I do have a small tiny sample database of this search working. You can input any number of the fields to filter out the data, give it a try!
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
Not a problem, glad it's helping you 
Let me know how it goes.
Let me know how it goes.
The mistake I was making was:
"( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
I didn't realize that "Country" referred to the textbox in the report. I thought it referred to the column in the report - and thus didn't matter. My mistake. Your code works exactly as I hoped.
Thanks for all the help.
"( [Country] = [Forms]![frmSearch]![txtCountry]) AND "
I didn't realize that "Country" referred to the textbox in the report. I thought it referred to the column in the report - and thus didn't matter. My mistake. Your code works exactly as I hoped.
Thanks for all the help.
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
Awesome! Glad it worked, you're very welcome.
Hello Devastation,
I'm sorry, but I have another question. I'm trying to use the same code to filter a listbox on my form, but when I hit the filter button, I get a blank listbox. Can you see what I'm doing wrong?
I'm sorry, but I have another question. I'm trying to use the same code to filter a listbox on my form, but when I hit the filter button, I get a blank listbox. Can you see what I'm doing wrong?
Code:
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
'Button filters out records depending on the criteria entered in the search fields
Dim strWhere As String
Dim lngLen As Long
'Stores the search criteria and enters it into the Where Condition - Log Reference
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldLogRef]) Then
strWhere = "([fldLogRef] = [Forms]![frmBase]![chdDetail].[Form]![fldLogRef]) And "
End If
'Stores the search criteria and enters it into the Where Condition - Country
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldCountry]) Then
strWhere = "( [fldCountry] = [Forms]![frmBase]![chdDetail].[Form]![fldCountry]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition - Date Requested
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldRequestDate]) Then
strWhere = "( [fldRequestDate] = [Forms]![frmBase]![chdDetail].[Form]![fldRequestDate]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition - Description
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldDescription]) Then
strWhere = "( [fldDescription] = [Forms]![frmBase]![chdDetail].[Form]![fldDescription]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition - Status
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldStatus]) Then
strWhere = "( [fldStatus] = [Forms]![frmBase]![chdDetail].[Form]![fldStatus]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition - Date Shipped
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldShipDate]) Then
strWhere = "( [fldShipDate] = [Forms]![frmBase]![chdDetail].[Form]![fldShipDate]) AND " & strWhere
End If
'Stores the search criteria and enters it into the Where Condition - Tracking #
If Not IsNull([Forms]![frmBase]![chdDetail].[Form]![fldTrack]) Then
strWhere = "( [fldTrack] = [Forms]![frmBase]![chdDetail].[Form]![fldtrack]) AND " & strWhere
End If
lngLen = Len(strWhere) - 5
'If there was no information included in the criteria fields an error pops up, otherwise the search processes the Where Condition
If lngLen <= 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
Forms!frmBase!chdList.Form.List0.RowSource = strWhere
End If
'Reverses Any Edits
Me.Undo
Exit_Command20_Click:
Exit Sub
Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click
End SubDevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
Ok I am slightly confused here.
You have a form that they select certain criteria from, and then a listbox only includes values that meets the criteria?
You're giving my brain a workout today I assume your RowSourceType is Table/Query.
Try using this line of code:
Forms!frmBase!chdList.Form.List0.RowSource = "SELECT [Table1].[fldLogRef], [Table1].[fldCountry], [Table1].[fldRequestDate], [Table1].[fldDescription], [Table1].[fldStatus], [Table1].[fldShipDate], [Table1].[fldTrack] FROM Table1 " & strwhere & ";"
Note that there IS a space after "FROM Table1 ". Table1 is the table the fields are from.
The reason your code doesn't work is because the RowSource is an SQL statement in the form of SELECT <field names> FROM <table> WHERE <stuff to filter>, however your code only states a WHERE condition and nothing to "SELECT", so in essense you're telling it "Select nothing, from no table... however try and filter these fields" which is why you're listbox comes up blank.
I hope this helps, and is what you're looking for. However if it isn't, I'm sure this is a good start
You have a form that they select certain criteria from, and then a listbox only includes values that meets the criteria?
You're giving my brain a workout today
I assume your RowSourceType is Table/Query.Try using this line of code:
Forms!frmBase!chdList.Form.List0.RowSource = "SELECT [Table1].[fldLogRef], [Table1].[fldCountry], [Table1].[fldRequestDate], [Table1].[fldDescription], [Table1].[fldStatus], [Table1].[fldShipDate], [Table1].[fldTrack] FROM Table1 " & strwhere & ";"
Note that there IS a space after "FROM Table1 ". Table1 is the table the fields are from.
The reason your code doesn't work is because the RowSource is an SQL statement in the form of SELECT <field names> FROM <table> WHERE <stuff to filter>, however your code only states a WHERE condition and nothing to "SELECT", so in essense you're telling it "Select nothing, from no table... however try and filter these fields" which is why you're listbox comes up blank.
I hope this helps, and is what you're looking for. However if it isn't, I'm sure this is a good start
Thanks again Devastation. I got it to work (although I had to add a "WHERE" into your line after the "FROM Table1").
You are really good at this stuff.
The only thing I have to figure out now is how to get my listbox to display detailed information in a subform like it did before, but I'm sure I'll find out why it stopped working.
------------------
Figured that out too.
You are really good at this stuff.
The only thing I have to figure out now is how to get my listbox to display detailed information in a subform like it did before, but I'm sure I'll find out why it stopped working.
------------------
Figured that out too.
Last edited:
DevastatioN
Registered User.
- Local time
- Today, 06:18
- Joined
- Nov 21, 2007
- Messages
- 242
Awesome I'm glad that worked for you!
Thanks for the compliment, I am a student in my final year of university for IT and work as a Database Analyst, so I am still learning too. There are many people around here that know more than me.
This place was the best I've ever found for finding code, and help from others.
Glad I was able to assist, good luck again!
Thanks for the compliment, I am a student in my final year of university for IT and work as a Database Analyst, so I am still learning too. There are many people around here that know more than me.
This place was the best I've ever found for finding code, and help from others.
Glad I was able to assist, good luck again!
