467,877 Members | 1,079 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,877 developers. It's quick & easy.

Using IsError() in query expression

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
  • viewed: 23655
Share:
1 Reply
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ellery Leung | last post: by
4 posts views Thread by uspensky | last post: by
1 post views Thread by Swincher | last post: by
3 posts views Thread by jk | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.