Ahh I know your pain! you are tying to create it via the inbuilt access query builder correct?
I find the access query builder to be fine for very simple things, however it quickly tends to over complicate matters, it really does it when you go it to edit it because it converts your SQL, into SQL that it understands (i have a copy on my desk of 15 a4 pages of an access built query, compared to a 2 line SQL statement generated by a function on my form).
In your case, I would place a
Code:
Private Sub Form_Open()
dim lsql as string
lsql="SELECT Items.ID, Items.QBItemNumber, MFGList.MFG, Items.Model, Items.ModelNumber, Items.HasUniqueID, Items.IsPhysical, Categories.Category, Categories.SubCategoryFROM MFGList INNER JOIN (Categories INNER JOIN Items ON Categories.ID = Items.Category) ON MFGList.ID = Items.ManufacturerWHERE (Items.ID=txtFilterItemNumber.Value OR Items.ID=txtFilterItemNumber.Value IS NULL) AND (Items.QBItemNumber=txtFilterQB.Value OR Items.QBItemNumber=txtFilterQB.Value IS NULL) AND (MFGList.MFG=cboFilterMFG.Value OR MFGList.MFG=cboFilterMFG.Value IS NULL) AND (Items.Model=txtFilterModel.Value OR Items.Model=txtFilterModel.Value IS NULL) AND (Items.ModelNumber=txtFilterModelNumber.Value OR Items.ModelNumber=txtFilterModelNumber.Value IS NULL) AND (Items.HasUniqueID=chkFilterUnique.Value OR Items.HasUniqueID=chkFilterUnique.Value IS NULL) AND (Items.IsPhysical=chkFilterPhysical.Value OR Items.IsPhysical=chkFilterPhysical.Value IS NULL) AND (Categories.Category=cboFilterCat.Value OR Categories.Category=cboFilterCat.Value IS NULL) AND (Categories.SubCategory=cboFilterSubCat.Value OR Categories.SubCategory=cboFilterSubCat.Value IS NULL);"
me.recordsource=lsql
End Sub
In your forms on open event, and then edit it manually.
By the looks of things though you have several controls you use to construct the query for your form
You might benefit from generating it by a function
I enclose an example here...
Code:
Private Function Gen_SQL()
'Declare variable
Dim lsql As String
'Set the constant beginning of the SQL
lsql = "SELECT * FROM [CONTROLLER TABLE] WHERE [SERVICING NO]=" & [Forms]![activeindex]![Servicing No]
'Assign extra bits of SQL as required from the combo boxes
If IsNull([SNOW]) = False Then
lsql = lsql & " AND [Snow]=" & [SNOW]
End If
If IsNull([Sheet]) = False Then
lsql = lsql & " AND [Sheet]=" & [Sheet]
End If
If IsNull([Combo28]) = False Then
lsql = lsql & " AND [Stat code]='" & [Combo28] & "'"
End If
If IsNull([cc status]) = False Then
lsql = lsql & " AND [CC STATUS]='" & [cc status] & "'"
End If
If IsNull([Planned pulse]) = False Then
lsql = lsql & " AND [Planned Pulse]='" & [Planned pulse] & "'"
End If
If IsNull([Status]) = False Then
lsql = lsql & " AND [STATUS]='" & [Status] & "'"
End If
If IsNull([Planner Seen]) = False Then
lsql = lsql & " AND [Planner Seen]='" & [Planner Seen] & "'"
End If
If IsNull([Zone Code]) = False Then
lsql = lsql & " AND [Zone Code]='" & [Zone_Code] & "'"
End If
If IsNull([Actual_Pulse]) = False Then
lsql = lsql & " and [actual pulse]='" & [Actual_Pulse] & "'"
End If
'add the constant ending to the SQL
lsql = lsql & " ORDER BY [SNOW] ASC, [SHEET] ASC, [LINE] ASC"
Gen_SQL = lsql
End Function
I then use a 'refresh' command button to allow the user to manually requery based on the newly generated SQL from the function by using.
Code:
Private Sub Cmd_ref_results
dim msql as string
msql=Gen_SQL
me.recordsource=msql
End Sub
I know this goes a bit beyond what you asked but I think you may find it useful (and if I'm really lucky someone may even be able to optimize my code for me, also note the names arent great as this is an early project I havent got round to renaming yet!)