How to work with "#error" result in a query

[David Jensen]

Hi,

I have a query (below) that is returning a text string. This query will
then drive a crosstab query. in the dynaset, a few of the records are
returning "#error" instead of the text string result. This is keeping the
crosstab from running. I want to convert the error to a non-error value
that can be used in the crosstab query but I can't figure out how to get it
to return something other than "#error". Any guidance would be appreciated.

The SQL statement is:

Test:
Mid([DocumentName],InStr(InStr([DocumentName],"-")+1,[DocumentName],"-")+2,I
nStr(InStr(InStr([DocumentName],"-")+1,[DocumentName],"-")+1,[DocumentName],
"-")-InStr(InStr([DocumentName],"-")+1,[DocumentName],"-")-2)

 

[david epsom dot com dot au]

You can't work with #error at all. you need to rewrite your
test so that you don't get negative numbers when inStr returns zero.
You can use IIF to test strings and numbers.

(david)

Hi,

I have a query (below) that is returning a text string. This query will
then drive a crosstab query. in the dynaset, a few of the records are
returning "#error" instead of the text string result. This is keeping the
crosstab from running. I want to convert the error to a non-error value
that can be used in the crosstab query but I can't figure out how to get it
to return something other than "#error". Any guidance would be appreciated.

The SQL statement is:

Test:
Mid([DocumentName],InStr(InStr([DocumentName],"-")+1,[DocumentName],"-")+2,InStr(InStr(InStr([DocumentName],"-")+1,[DocumentName],"-")+1,[DocumentName],
"-")-InStr(InStr([DocumentName],"-")+1,[DocumentName],"-")-2)

 

[Guest]

use drop down in field name make sure you have right name also check you record souce for the field in your report