Help with IIF

[kmclane]

I have a field on a report that shows the Salesrep code. The underlying table field is text, but only 2300 out of 7400 records have an entry. I want to display "NA" when ther is no entry for that field. My query shows an empty field when run, I have tried quite a few things but I only get a #Error. This is my latest attempt:

=IIf([SalesRep]=" ","NA",[salesrep])

But apparently it is not a space. I have tried IsNull, IS Empty, ""(zero Length string). Any suggestions?
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Frink]

Hallo,

=IIf(IsNull([SalesRep]),"NA",[SalesRep])

should work, or

=IIf(len("" & [SalesRep])=0,"NA",[SalesRep])

Alternatively you could change the Format of the displayed field to display NA when blank or null

- Frink

 

[kmclane]

Frink, I had already tried the IsNull one, but I got the same #Error when trying your other suggestion. I get the error whether or not the field has data. This should at least display the data when there is an entry. If I just reference the field, I see the data when it is there.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Frink]

Hallo,

What about the Format of the report field. Setting it to:
@;"NA"
should do the trick.

- Frink

 

[kmclane]

That worked, Now I have to find another solution. I am trying to get a total per salesrep. I created a SalesRep footer, I have a SalesRep group, added a text box and made it a running sum. I have tried referencing my calculated textbox directly, and adding an invisible textbox that references it and using that as the control source. I have one salesrep that has a total of $110.81, but the last page in that group has $0.00 in the calculated text box, and my running sum is returning $0.00. I have my running sum set to over group. I'm not sure where the problem lies.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Frink]

Hallo,

You might be better re-submitting this as a new post as I never use MS Access reports, so know nothing about them!

- Frink

 

[kmclane]

Yeah, I'll do that if I decide to pursue this, got another project I need to do first. Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg