Filter query based on comboboxes

[number1pita]

Hello,

I have the following options in a query:

Year
Category Type
Category ID
Office

I would like to have four combo boxes on a form where the end user can choose the year and click to export the query to Excel showing all fields with the selected year. I would also like the ability where if the end user wants to select a specific year and a specific category type they can choose their options and export the query to Excel showing a report of only that specific category type in that specific year they chose.

I was able to get the first combobox to work, but when I created the second combobox and I try to click on the drop down, there are no options that populate.

Any help would be greatly appreciated!

 

[Joe4]

Regarding the link I provided, if you have situations where the user might not be selecting all the fields on the Form, you would just enclose each criteria building step of VBA in an IF ... THEN statement where you first check to see if there is anything in that text box/combo box before adding that part to the criteria you are building.

 

[number1pita]

I don't quite understand this part:

' Add first criteria (numeric example)
myCriteria = " [Field1]=" & Me.TextBox1

' Add second criteria (string example)
myCriteria = myCriteria & " AND [Field2]=" & Chr(34) & Me.TextBox2 & Chr(34)

this is what I have typed into my code, and I am super confused on where to go from here, sorry:

Private Sub cmdButton_Click()
Dim mySelect As String
Dim myCriteria As String
Dim mySQL As String

' Get basis of SQL code
mySelect = "SELECT Qryreportinfo.* FROM Qryreportinfo WHERE"

' Add first criteria (numeric example)
myCriteria = " [year]=" & Me.cmboyear

' Add second criteria (string example)
myCriteria = myCriteria & " AND [type]=" & Chr(34) & Me.Combo11 & Chr(34)

' Build complete SQL string
mySQL = mySelect & myCriteria";"

' Assign SQL to query
CurrentDb.QueryDefs("Query2").SQL = mySQL

' Open report
DoCmd.OpenQuery "qryreportinfo", acViewPreview, "", ""

End Sub

my "report" is actually a query, which is qryreportinfo. That is the query that I am having my criteria from the combo boxes define, that is ok correct? Year is a field with combo box cmboyear, type is a field with combo box combo11, CatID is a field with combo box cmbCatID, and VMO is a field with combo box cmbVMO.

Can you help me fix my code with that information?

 

[number1pita]

yikes, I am definitely going to have to do the if/then, that is definitely going to complicate things a bit huh

 

[Joe4]

It doesn't matter if it is a Report or not. Basically, you could have four end-result scenarios:
- Open a Report based on the Query you are building
- Open a Form based on your Query you are building
- Open your Query
- Export your Query

yikes, I am definitely going to have to do the if/then, that is definitely going to complicate things a bit huh

Its really not that bad.

Just choose the appropriate command at the end of code that reflects what you want to do.
For example, in the first one:

[COLOR=#333333]' Add first criteria (numeric example)[/COLOR]
[COLOR=#333333]myCriteria = " [year]=" & Me.cmboyear[/COLOR]

You could rewrite it like this:

[COLOR=#333333]' Add first criteria (numeric example)
[/COLOR]If Len([year])>0 Then
[COLOR=#333333]    myCriteria = " [year]=" & Me.cmboyear[/COLOR]
End If

 

[number1pita]

Well, I just had my meeting, and I have found out this might get even more complicated. They want the ability to pick and choose which fields from a query that they want in the report, and then pick and choose criteria for those fields. I am not sure how to go about doing this. Any suggestions?

 

[Joe4]

Use the same logic in building your SQL string, only now you are going to have to build each field in the same manner you are building the criteria. I would just list every single field on the form, and have a checkbox next to it, where they can choose the fields they want to include.

Not much harder that what you are doing with the criteria, just a lot longer. It seems like a silly request though. I'd have half a mind to dump everything to Excel and teach these people how to use Filters and Hide Column.

 

[number1pita]

Haha! You read my mind on that one! However we have people who do not do well at all in Excel and it is kind of scary. Basically we have a different type of relational database that houses the majority of our data, and this specific data being housed in an Access database won't be able to hold in our other database, and they want to be able to pick and choose their fields they want in their report just like our other database. If it were me it would be a quick run of a select query giving me all the fields, then I would just delete or hide the columns and filter on the criteria, but instead for some reason I am having to build this out in an Access database, which I have definitely worked in Access, just not as experienced as I wish I was, in order for the report to pull up exactly how they pick and chose for it to pull up. grrr