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

  • Thread starter Thread starter David Jensen
  • Start date Start date
D

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)
 
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)

David Jensen said:
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)
Click to expand...
 
use drop down in field name make sure you have right name also check you record souce for the field in your report
 
Back
Top