Covering up or Hide my #Error !

[nowanda]

Hello All!

Because of poorly entered data (I know - it's a poor workman who blames his tools!)- I've got a funny question:

I have a query which results in data resembling:

34
#Error
23
#Error
4.6
22
36

I know the #Error is there. There actually isn't valid data for that field - it's sort of a divide by 0 which I don't want to do - is there a way to "hide" the #Error - to mask it - make it null, etc?

Cheers!
Nowanda

 

[Corticus]

Hi Nowanda,

I'm little confused, Access won't let an expression attempt to divide by zero in a query, it won't even run the query, it justs gives you an error. How did these #error get in the query? Are they the result of an expression?

If so, you need to filter out the values causing this error.

Say field1 is causing expr2 to error.

Go to your query, add an expression like:

expr1: =iif(isnull([field1]) or [field1]=0 or [field1]="","",[expr2])
so it looks for the offending value, and if its in that record, the expresion returns "", if its not, it returns expr2, which whould work for this record.

Again, I don't know how you're getting that value in a query through division, since Access won't let you run a query that produces a div/0 error, so I might be going the wrong way...

HTH,

 

[nowanda]

Howdy Corticus!

I can clarify... the actual command which either gives me the correct value or the #Error is:

Left([Field1],InStr([Field1],";")-1)

So it's sort of a function error - but how to trap? It's on data like the following:

KEY ID Field1
11 23
12 34 34.6;23;
13 46 345;257;

Here would be the result of the query:
#Error
34.6
345

Any tips - I could ask the user to simply add two ";"'s when there are blank values for field one - but is there another way to just "mask" the #Error?

Nowanda

 

[Corticus]

Okay,

Try this:
IIf(IsNull([field1]),"",Left([Field1],InStr([Field1],";")-1))

This way, if the field that is causing the error on null is null, the formula doesn't get run, a "" is shown, otherwise, the formula runs and returns the value you need.

HTH,

 

[nowanda]

Sweet!

It struck me on my way back from lunch to do this:

IIf([Field1]="",[Field1],Left([Field1],InStr([Field1],";")-1))

And just like your response - no Errors!!

Thanks a million!

Nowanda