PDA

View Full Version : Solved: send null value to a function



khyber
07-24-2005, 01:48 PM
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:

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

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
07-25-2005, 02:47 AM
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
07-25-2005, 08:43 AM
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:

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

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
07-25-2005, 08:49 AM
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
07-25-2005, 11:21 AM
"*" 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
07-25-2005, 01:06 PM
Can we see the actual SQL for the query?

xCav8r
07-25-2005, 04:35 PM
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.

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

khyber
07-26-2005, 10:06 AM
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
07-26-2005, 10:15 AM
How do I close this thread and mark it ANSWERED???

xCav8r
07-26-2005, 10:16 AM
Look for the link to thread tools at the top of this thread.