Using 'IIf'

[Matt Greatorex]

I have about ten fields on a report. Each currently gets their value from a table I've created. This worked fine until I had to increase the number of fields to a point where they won't all fit.

As the table holds information on which employees are missing information, I would like to simply display an 'X' if the field value is null, and a '-' if it isn't.

The problem I have is using the IIf functionality.

The original ControlSource for one of the fields was

[Seniority].

I changed this to

=IIf(IsNull([Seniority]),'X','-')

Unfortunately, what happens is that all records are being shown as '-', regardless of whether or not their value is null.

Is there some setting I should be aware of that might be affecting my results? Is my syntax incorrect? I'm hoping it's something simple, as I can handle looking like an idiot as long as I get the report working.

 

[nateobot]

=IIf(IsNull([Seniority]),'X',[Seniority])

Change it to read as above.

the iif statement works like this:

IIf([condition], if condition is true do this, otherwise do this)

In your case if there was data it was putting in a '-' which is what you told it to do.

 

[Matt Greatorex]

Sorry, maybe I wasn't being clear.

I want it to display a '-' if there is data - since these records are of no concern - and to display a 'X' if there is none.

Displaying the actual value is making the report look very cluttered, as there are too many characters in most of the fields to be properly displayed.

 

[nateobot]

Gotcha, thanks for the clarification.

=IIf(IsNull([Seniority]) Or [Seniority]="",'X','-')

Try that. Could be that they are empty fields rather than Null fields.

 

[Rickster57]

IIf

You may also want to try iif(isnull(trim([Seniority} etc... Trim will remove any empty spaces

 

[Matt Greatorex]

Thanks, both.

As far as the suggestions go, it's no luck on both counts, I'm afraid.

All fields still being displayed as '-', regardless of their value/lack thereof.

 

[Rickster57]

null

iif(isnull(trim([Seniority],"X","-"))

If this doesn't work then the field you are using probably isn't the name of the field in the report. If [seniority] is not the name of the field then - will always print out.

Make sure you are using quotes, not '.
Good luck.

 

[Matt Greatorex]

Thanks for the help, anyway.

I'm assuming that since using [Seniority] on it's own displays a value, the name must be correct. I shall keep playing with this, as and when I get a chance.

If I should find anything out, I'll let the forum know.