Using IsError() in query expression

Tooled

I am writing a vb.net app that uses ms-access as its database.

I need to allow users to create their own custom sql expressions that
will eventually be used in their reports. After they have created
their expression, I will store the expression in a db table (as a
string). Now when they go to create a report, they can choose one or
more expressions (that they have previously created) that will be used
to gather numerical data which will be displayed in their graphical
report.

The problem here is that the expression they create may create an
error at runtime (i.e. divide by zero). To avoid any runtime #Error's,
I attempted to wrap their defined expression with IIF() and IsError().
For example: If the user defined the expression as:

"SELECT A.foo / B.bar"

I tweeked the string to read:

"SELECT IIF(IsError(A.foo / B.bar,NULL,A.foo / B.bar))"

If/when I run the tweeked version of the expression and "B.bar" = 0, I
would receive an #Error value, otherwise, the expression fired off
w/out problems.

My question, finally, is how can I trap for "any" error that the
expression may cause? And, if an error does occur, how do I assign
that individual record a value of NULL? In other words, I don't want
the entire record set to fail due to one error prone record.

Keep in mind that I'm trying to avoid hard coding a condition as in:
IIF(B.bar = 0,NULL,B.bar) - for me to use this method would mean that
I'd have to write an expression parser - which, to be honest, is a
little out of my league (not to mention, I'm too lazy).

Nov 13 '05 #1

MA

"Tooled" <ho*****@gmail.com> ha scritto nel messaggio
news:57**************************@posting.google.c om...

I am writing a vb.net app that uses ms-access as its database.

I need to allow users to create their own custom sql expressions that
will eventually be used in their reports. After they have created
their expression, I will store the expression in a db table (as a
string). Now when they go to create a report, they can choose one or
more expressions (that they have previously created) that will be used
to gather numerical data which will be displayed in their graphical
report.

The problem here is that the expression they create may create an
error at runtime (i.e. divide by zero). To avoid any runtime #Error's,
I attempted to wrap their defined expression with IIF() and IsError().
For example: If the user defined the expression as:

"SELECT A.foo / B.bar"

I tweeked the string to read:

"SELECT IIF(IsError(A.foo / B.bar,NULL,A.foo / B.bar))"

If you rename the field you have to specify an alias
SELECT IIF(IsError(A.foo / B.bar,NULL,A.foo / B.bar)) as Pippo (J'm Italian,
we use Pippo not Foo)

ciao
MAssimiliano

Nov 13 '05 #2