Where [field]=variable

[project2501]

I am trying to create a query with the WHERE criteria equaling a variable defined in vba. The variable in vba is set to equal the .value of a combo box. However, if the combo box value is null, then it gives me an error saying it's an invalid use of null. How do I code the variable so if the combo box value is null, then the variables value is one that will return all records for the related field in the query? Or how do I set the combo box to include an option for all records (which would be set as it's default value)?



I know how to achieve this outcome by defining it all in the query, but I am trying to reduce the query size by turning WHERE criteria into vba variables to escape "too complex" errors

Thank you for any advice/tips offered

[ranman256]

you need 2 queries:
1 without the null combo, where the sql doesnt use it,
qsDataAllDepts
select * from table

1 with the combo as criteria:
qsData1Dept
select * from table where [dept] = forms!myForm!cboDept

then to open the query:

sub btnOpen_click()
if IsNull(cboDept) then
   docmd.openquery "qsAllDepts"
else
   docmd.openquery "qs1Dept"
endif
end sub

[June7]

You tried a dynamic parameterized query? Why would you get 'too complex' error?

Is the field a text type? Try LIKE and wildcard in query dynamic parameter.

WHERE fieldname LIKE Forms!formname!comboboxname & "*"

Or use the query as RecordSource for form or report and apply filter to the form or report.

DoCmd.OpenReport "report name", , acViewPreview, "fieldname LIKE '" & Me.combobox & "*'"