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 <--

 

[JimBurke via AccessMonster.com]

Thought you had that all straightened out. Are you talking about that same
SQL,with City, County, State and tax code? There's no reason the SQL
shouldn't work if all four fields are used in the WHERE clause, so you must
still have an error in the SQL or in your VBA logic. There shouldn't be any
need to change it to use a function. Let me know if you're still talking
about that same SQL, it should be fixable. What error were you getting that
caused you to change your approach?

 

[JimBurke via AccessMonster.com]

Do you have experience using the Access query builder, i.e. going to the
Query tab and creating a new query? If so, if you feel like taking a couple
of minutes, try this. Create this query using the query builder (select the
tables, join them as you have them joined in the SQL, drag the appropriate
fields onto the query. Include all fields you are selecting as well as all
fields you need WHERE criteria for. Sounds like a lot, but it should only
take a couple of minutes as long as you're familiar with the query builder.
In the four fields you specify criteria for (I think they're city, county,
state and tax rate or something like that), specify criteria that you know
would find a match. e.g. for State put criteria of "MI", etc. Run the query.
Make sure you put in criteria for all four! You should get some results. As
long as that works (no reason it shouldn't), go back into design mode in the
query and do a View, SQL View. This will show you the SQL for the functioning
query. Copy and paste that back in here and I can build the query for you in
VBA code with all the Ifs that build the WHERE clause. It would be fairly
simple for me to do that if I have the working SQL statement with the
criteria hardcoded in it. When I want to build SQL in VBA that uses multiple
joins I usually create the query thru the query builder first, then copy and
paste the SQL into my VBA code and make the necessary adjustments.

 

[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!