Query Criteria via Function

[bschulze]

I'm trying to provide the criteria for a query via a function, only because I could not get the like(isnull(iff statement to work properly. I have a form where the users select specific filter criteria for a report, I then read the forms data and pass the value to a query.

[CODE ]
Function FilterCompanyCode() As String
If IsNull(Forms!frmReports.cboEntity) Then
FilterCompanyCode = "Is Null Or """" Or Like ""*"""
Else
FilterCompanyCode = [Forms]![frmReports].[cboEntity]
End If
End Function
[/ CODE]

The FilterCompanyCode() is in the queries criteria. When the value in the combobox is not null the query returns the desired records. When the combobox is null the query does not return any records. When I step through the function and copy the variable directly to the query, it returns the proper recordset.

Anyone have any ideas why it wont work while running the function but does work when copied into the criteria of the query. Any help is greatly appreciated.

 

[bschulze]

I have a solution, will try to recap for anyone searching in the future.

Need to pass a value from a user form to a queries criteria. Desired results, if not null then user value, else all records (including Null values).

The solution I found was to break up the or statements into multiple lines.

Original Try:
IIF(IsNull([Forms]![frmA].[cboB]),Is Null Or "" Or Like "*",[Forms]![frmA].[cboB]

Solutions:
Criteria 1 = [Forms]![frmA].[cboB] Is Null
Criteria 2 = [Forms]![frmA].[cboB]


Break the statement into two criteria lines.

Alternatively, if you want to display the null values when the user has selected a criteria
[Forms]![frmA].[cboB] Or Like [Forms]![frmA].[cboB] Is Null


Hope that helps anyone interested, cant help you as to why one works and the other doesn't.