[AccessD] #Error

I decided to do the following.

Function AvoidError(n As Variant, Optional varRetOnError As Variant = "")
         
    On Error GoTo Trap
    AvoidError = n
    Exit Function

Trap:
    AvoidError = varRetOnError
    Resume Next

End Function

I then placed that in the query that wqas displaying #error and in fact it
never even bothered to call the function, it simply reported #error.

In my case I can use the function anyway, but use it in my function that
builds up the output file.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, December 22, 2006 11:39 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] #Error

John,

Define the column using the function below.  You can have it return whatever
you want.

Jim.

Function AvoidError(n As Variant)
         
    On Error GoTo Trap
    AvoidError = n
    Exit Function

Trap:
    AvoidError = 0
    Resume Next

End Function 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, December 22, 2006 11:10 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] #Error

I am building up a query from subqueries.  One of the subqueries comes from
a SQL Server view, linked to Access.  When I do an outer join on that
subquery, I get #error in the fields for that subquery in any record where
there is no data coming from the SQL Server linked table.
 
Has anyone ever seen this before?  It looks similar to cases where you link
to a spreadsheet and the data type changes down the table.
 
It is ugly and I do not want to (can't really) send this out.  The end
result will be a text file sent to a bank or insurance company for automatic
import into their database.  I have never found a way to turn that #error
thing into any type of data that could be replaced with something else.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com