Question

Q: How to handle #Error return on Query? This thread is locked from future replies

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?

Answer

A:

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/

Did this solve your problem?

Sorry this didn't help.

1 person was helped by this reply



 
Question Info

Views: 14562 Last updated: February 15, 2018 Applies to: