Solved: send null value to a function

[khyber]

I have a query which receives a parameter from a form. The criteria is set to fmSetParam()

The user selects a BI number from a combo box. If however, the user wishes to view all (no filter on BI) then
I wish the function to return a Is Not Null to the fmSetParam. It does not work. How do I pass a Is Not Null to the function that is usable in the query?

The statement I have so far is:

[vba]Function fnSetParam() As String
Dim strInput As String

If Forms!fmSelect!txtBI = "" Then
strInput = "Is Not Null"
Else
strInput= Forms!fmSelect!txtBI
End If
fnSetParam = strInput
End Function[/vba]

I have declared the strInput in the form's module. It works for any number I enter in the input box but not for a null value. In the event of a null value, I wish the query to return ALL records in the recordset.

[Brandtrock]

Welcome to VBAX!!!

Please use code tags when posting code. The green and white VBA icon on the reply toolbar will do this for you. It makes reading posted code much easier.

Thanks,

[khyber]

I have a query which receives a parameter from a form. The criteria is set to fmSetParam()

The user selects a BI number from a combo box. If however, the user wishes to view all (no filter on BI) then
I wish the function to return a Is Not Null to the fmSetParam. It does not work. How do I pass a Is Not Null to the function that is usable in the query?

The statement I have so far is:

[VBA]Function fnSetParam() As String
Dim strInput As String

If Forms!fmSelect!txtBI = "" Then
strInput = "Is Not Null"
Else
strInput= Forms!fmSelect!txtBI
End If
fnSetParam = strInput
End Function[/VBA]

I have declared the strInput in the form's module. It works for any number I enter in the input box but not for a null value. In the event of a null value, I wish the query to return ALL records in the recordset.

[Norie]

Why not use the * wildcard instead of Is Not Null?

I think if you use "Is Not Null" Access will actually look for that string not the expression.

[khyber]

"*" will not work. neither will NZ([BI]). no matter how I code it, it will accept anything - even "brocolli" - but not anything that refers to <> Null

Thanks, Norie

[Norie]

Can we see the actual SQL for the query?

[xCav8r]

Make your life easier and forget about using a parameter with the GUI for queries. Just change the source of whatever you want to the SQL statement the function below generates.

[VBA] Function fnSetParam() As String
Dim strSqlBase As String
Dim strSqlWhere As String

strSqlBase = "SELECT tblSample.Field1, tblSample.Field2," _
& " tblSample.Field3 FROM tblSample"
If Forms!fmSelect!txtBI = "" Then
strSqlWhere = " WHERE Not IsNull(tblSample.Field1);"
Else
strSqlWhere = " WHERE tblSample.Field1 =" & Forms!fmSelect!txtBI & ";"
End If

fnSetParam = strSqlBase & strSqlWhere

End Function [/VBA]

[khyber]

Allright, I guess I will do that. Thanks xCav8r. You like like a brilliant guy so I will take your advice.

Thanks to you to Norie, for your time and your interest.

bonsiur

[khyber]

How do I close this thread and mark it ANSWERED???

[xCav8r]

Look for the link to thread tools at the top of this thread.