How to handle #Error return on Query?

In have a query which returns a column based on a call to a bespoke function in a module which returns TEXT.  In the query, some of the other columns are passed to the function for the function to do its thing (complex alogrithm).  When the passed columns are empty (I presume they are NULL), the function returns #Error to display in the query answer table.  

I'm ok with getting an error return as thats true.  The algorithm can't compute unless all fields available.  However, I'm trying to append these query results to another Table and the field where there function result goes is a Text(255) field.  However, Access will not allow the append of what looks to be text into this text field.  

What is the form of this #Error "text" that get's returned?  How to turn it into real text that can be saved?

I have tried:

: use an IIF to test for True/False on the return

: use an IIF to test for =#Error

: put On Error error handling in the function to go to a part of the function to set the return value = "ERROO!" which I know is text

Ideas?

 

Question Info


Last updated November 6, 2018 Views 16,424 Applies to:
Answer

You do not need on error. Having no values available is just a situation you have to handle.

However, you do have to define both the parameters as the function as Variant to make sure nulls are accepted. The function could look like:

 

Function YourFunction(A AS Variant, B AS Variant) AS Variant

IF IsNull(A) and IsNull(B) Then

   YourFunction=Null

   Exit Function

End If

 

.

.

.

.

.

.

End Function


Peter (http://access.xps350.com/)
Peter

http://access.xps350.com/

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.