trap "#Error" in query 
Author Message
 trap "#Error" in query

Is there any way to trap an #Error in a query?  I'm trying to test an
alpha field for the presence of a numeric value.  If I use CDbl
([MyNumber]), I get an #Error for those null or non-number values in
the field.  I'd like to turn that #Error into a zero or null, but it
seems to propigate through any type of expression.

I've tried writing a function using IsError, On Error and none of them
appear to allow me to be able to do what I'm trying to do.  Any ideas?

TIA,
Nolan Madson

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Mon, 28 Oct 2002 03:00:00 GMT  
 trap "#Error" in query

Quote:

> Is there any way to trap an #Error in a query?  I'm trying to test an
> alpha field for the presence of a numeric value.  If I use CDbl
> ([MyNumber]), I get an #Error for those null or non-number values in
> the field.  I'd like to turn that #Error into a zero or null, but it
> seems to propigate through any type of expression.

> I've tried writing a function using IsError, On Error and none of them
> appear to allow me to be able to do what I'm trying to do.  Any ideas?

> TIA,
> Nolan Madson

Hi Nolan,
try using the IIf-Function with IsNull.
Like: IIf(IsNull([MyNumber]);"";CDbl([MyNumber]))

HTH
Eric

Sent via Deja.com http://www.deja.com/
Before you buy.



Mon, 28 Oct 2002 03:00:00 GMT  
 trap "#Error" in query
Try using a function along these lines:

Function AvoidError (n As Variant)

    On Error GoTo Trap
    AvoidError = n
    Exit Function

Trap:
    AvoidError = 0
    Resume Next
End Function

  What I'd do is add a 2nd argument to pass in the type of conversion that
you want to perform, and possibly a 3rd with the value you'd like as a
replacement if the conversion fails.

HTH,
Jim.



Mon, 28 Oct 2002 03:00:00 GMT  
 trap "#Error" in query
Yes, that should work is well, but I avoid IIF() like the plague as both
parts of the expression (true/false) are evaluated.  Although as I type
this, alarm bells are going off.  I seem to remember that one of the
versions changed this?  Ugh...definitely getting old...need to check on that
one (or if someone can chip in with a definite answer...).

Jim.



Mon, 28 Oct 2002 03:00:00 GMT  
 trap "#Error" in query
also you can use iif(iserror(..),...,...)
Quote:

> Is there any way to trap an #Error in a query?  I'm trying to test an
> alpha field for the presence of a numeric value.  If I use CDbl
> ([MyNumber]), I get an #Error for those null or non-number values in
> the field.  I'd like to turn that #Error into a zero or null, but it
> seems to propigate through any type of expression.

> I've tried writing a function using IsError, On Error and none of them
> appear to allow me to be able to do what I'm trying to do.  Any ideas?

> TIA,
> Nolan Madson

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Tue, 29 Oct 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. trap "duplicate key" errors

2. Error trapping a "Find" dialog

3. Trap "No record found error"

4. Trapping "Out of Memory" error

5. Update Query "Not Updatable" Error

6. ""Declare" query

7. Trapping "Expand", "Open", "Explore" in Windows Explorer?

8. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

9. trap "append table" msg

10. Trap Word "Fine In Use" message

11. Trapping the click on the "X"

12. Trapping a click on the "X"

 

 
Powered by phpBB® Forum Software