Query default not working

[Guest]

Hi,
data = Year-1 is an amount or "N/A"
data = S1 is the sign +,-, or blank for N/A

I am trying to get around an error condition which comes up when I divide my
amount by 100 to get cents on my table. I separated the sign from the
amount. I made a query to try to get around this error because I need that
"N/A" to appear, if S1 is not a + or -. This is what my field line is on my
query

Yr-1: IIf([S1]="+",[Year-1]*1/100,IIf([S1]="-",[YEAR-1]*-1/100,"N/A"))

Why doesn't it default to N/A when the other 2 condition are false? Instead
I get #ERROR

Help please

 

[Tom Ellison]

Dear Jube:

I'l like to suggest you build this up a little piece at a time to find out
where the problem occurs, rather than having a complex thing and a lot of
uncertainty where the problem occurs.

First, it appears you have a column named [Year - 1]. Using arithmetic
operators within column names would be a bad thing in my opinion. It makes
reading this a real mess.

So, for the first step of debugging the problem, please use this:

Yr-1: [Year - 1]

Is that OK? Next:

Yr-1: IIf(S1 = "+", "Yes", "No")

Is this OK? Next:

Yr-1: IIf(S1 = "+", [Year - 1] * 1 / 100, "N/A")

Build up to your finished product a piece at a time.

Can [Year - 1] or S1 ever be NULL? Write a quick scratch query to test for
these.

Tom Ellison