Hello everyone. This is my first post here. I've never taken any classes on Access 2007 and all my knowledge comes from the HELP file, forums like this and painstaking experience. The wizards are a life saver and as far as VB or SQL goes, I'm pre-kindergarten level.
So, now that you know my knowledge base, here's my question.
My goal is to use a form with multiple combo boxes to filter a query and return a subform as well as an option to print a report or export the query to Excel.
-The form is called Job_Shopper_Frm.
-In it are four combo boxes tied to premade tables that contain the criteria information for the query.
-There is also a text box with calendar function for "Date" criteria in the query.
-The query is called Job_Shopper_Qry.
-The query pulls from the table @@Master_All_Service_Tbl.
In the "Department" field of Job_Shopper_Qry is criteria tied to one of the combo boxes in the form Job_Shopper_Frm.
And this works great when I've selected a Department from the drop box.
The problem is, if I don't select an option from the drop box and leave it blank, the query returns a blank result. No records. Now, I understand that this is because there is no Department called "Blank" (you know what I mean).
How do I get the criteria to return all records if the combo box is blank?
For your amusement, I tried these today...
IIf([Forms]![Job_Shopper_Frm]![Combo_Department] is null, is null, [Forms]![Job_Shopper_Frm]![Combo_Department])
IIf([Forms]![Job_Shopper_Frm]![Combo_Department] = "", is null, [Forms]![Job_Shopper_Frm]![Combo_Department])
If the combobox. listindex returns -1, do a query returning everything, else do a restrictive query (e.g. criteria in query = [forms]![frmChoice]![cboStuCh].[value]). You may have to use two queries; one with something like [forms]![frmChoice]![cboStuCh].[value] and one without any "criteria" (so it returns everything).
Perhaps this code is of use. The first combobox is used to load a second combo box:
Private Sub cboPeriod_AfterUpdate()
Dim strSQL As String
Dim aValue As Integer
aValue = Me.cboPeriod.ListIndex + 1
'the listindex starts at 0; items in our list start at 1
strSQL = "Select id, period from periods where type = " & aValue & " order by id" 'order by id because data from text files
I tried everything praxis1949 suggested and still no dice. It may be that my knowledge of VB and macros is not sufficient. Any other ideas? I still got nothing. I don't want to give up on this project as a hopeless quest.