Using a function in an Access Query... handling null

[ThriftyFinanceGirl]

I have been working on a filtering form that will also be used as an
append/update by group form.

I tried building the SQL in VBA however, when I finally get all four
parameters in the SQL won't work. Works with Three parameters, but not with
four... same code, same scenario.

So.....
I'm trying to use a function in an Access query, Easy enough. HOWEVER, it
doesn't work when the user has NOT chosen one of the values (ie. the function
returns a "")... Evidently the query doesn't like this. How do I get the
function to put a value in as a parameter if it is chosen and otherwise no
parameter?

If you want to see the SQL and other code I was using before the errors that
we can't seem to fix, please look at the question "RUNTIME ERROR 2001" (July
2nd)... Any help appreciated!

 

[Stefan Hoffmann]

hi,

I have been working on a filtering form that will also be used as an
append/update by group form.

I tried building the SQL in VBA however, when I finally get all four
parameters in the SQL won't work. Works with Three parameters, but not with
four... same code, same scenario.

Of what kind of function are you talking?

There are two things which need to be considered when using user defined
function aka self written functions in VBA:

1. Can one or more parameters be NULL?
The normal data typs of VBA cannot handle NULL, e.g.

Public Function doSomethingWithNull(AValue As Integer) As Integer
End Sub

while raise an error as an integer cannot represent NULL. To handle it,
you have to use a Variant:

Public Function doSomethingWithNull(AValue As Variant) As Variant

If IsNull(AValue) Then
' Handle NULL.
doSomethingWithNull = Null
Else
If VarType(AValue) = vbInteger Then
' Process your value.
doSomethingWithNull = AValue * 1031
Else
' Handle wrong data types.
doSomethingWithNull = Null
End If
End If

End Sub

2. Do I have parameters not needed in all cases?

E.g.

Public Function havingOptionalParameters( _
AValue1 As Variant, _
AValue2 As Variant) As Variant

End Function

You can change it to:

Public Function havingOptionalParameters( _
Optional AValue1 As Variant = Null, _
Optional AValue2 As Variant = Null) As Variant

'Handle values using IsNull() according to your logic.

End Function



mfG
--> stefan <--

 

[ThriftyFinanceGirl]

Thanks Stefan,

I understand your concept, but I'm not sure how to define "AValue".... this
value should be the value that I'm pulling from the form control correct? If
so, How do I do that?

 

[Stefan Hoffmann]

hi,

I understand your concept, but I'm not sure how to define "AValue".... this
value should be the value that I'm pulling from the form control correct? If
so, How do I do that?

I can only repeat myself:

Of what kind of function are you talking?

Post code, SQL or VBA. Otherwise I'm really sure I cannot give a good
advice, cause I'm not sure what your exact problem is.


mfG
--> stefan <--

 

[ThriftyFinanceGirl]

thanks Jim,

You and I got it figured out yesterday... when I was in a crunch time I was
going to try to do it another way (hence the second post)... I don't know how
to "close" a post here so that is why it was still going on. Thanks so much!