Problem with a VBA Function Call in an Access Query

Question

Hi,

I have a query in Access where the results of one of the fields is based on whats returned by a function call with three parameters.  The parameters are fields in the table.  There's only one table in the query.  When I run the query, I get error message 3071, which essentially says the expression is too complex.  I've isolated it to the function call.  I have a similar query elsewhere, but it only has one parameter.  If I remove the fields as parameters and replace it with hard-coded text, the query runs without a problem.  The field as it exists in the query design view window is listed below:

Referral Source: sGetReferralSource([Referral_Source_Facility],[Referral_Source_MD_Office],[Referral_Source_Other])

The three fields are defined as text in the table and the receiving function in VBA is expecting three string parameters and it returns a string.  It seems to be pretty basic, standard stuff, so I have no idea what could be wrong.  Again, the three parameters are fields as they are named in the table.  There are a number of things I can do to work around the problem, so I'm not looking for workarounds.  I really want to know why this isn't working.  Any help or suggestions would be appreciated.

Eric

Answer 1

Well first, if its a VBA message it has to be in VBA code. So it has to be a function, not the SQL. 

As Bill suggests, the first thing is to check in any of the fields are null. If they are you might want to use the NZ function:

sGetReferralSource(Nz([Referral_Source_Facility],""),Nz([Referral_Source_MD_Office],""),Nz([Referral_Source_Other],""))

Answer 2

It's not a bug, it's a feature :-{)

A variable of datatype Text will not accept a NULL value. It can accept a zero length string "", but if you're passing a table field as an argument to a function, then either the table field must be non-Null or the argument must be defined as Variant.

Answer 3

Scott,

It's not a VBA message.  The error is generated by the query.  It never gets to the VBA code.  Bill also didn't suggest checking for null.  He said to change the parameters in the function, which worked.  It's still a potential bug in Access because the exact same query with only one parameter (Facility), doesn't generate the error.  While troubleshooting the problem, I tested the query with each field individually and it didn't generate an error.  The error only occurs when the three parameters are sent in the function call.  I do like the Nz function, though.

Eric

Answer 4

Bill,

That fixed it!  Interestingly, based on the results of the query, none of the fields actually contained null ( I check for nulls in the function), but Access didn't like the three parameters being received as a string.  As soon as I changed them to Variant in the function definition, the problem went away.  This seems to be a potential bug in Access.

Eric

Answer 5

Eric

Could it be that one or more of those 3 fields is null in certain records? If that is the case you will have to change the parameters in the function from string to variant and write your code so it can compensate for that.