iif

[JEB]

Sorry if this is simple - but it is Friday afternoon - I am sick - and probably simple myself.

I have a table with a column that has two values - one is empty and the other is "NY".

When I print my report I want the empty (or blank) to show as PA and NY to show as NY

I have tried =IIF([state]="", PA, [state])

but it does not recognise "" as a blank cell in the table.

What am I missing and what do I need to do?

Thanks,
Jeb

 

[honda882000]

Use the Nz function instead. It replaces null values with a value specified. If it is not null, it will return the field value.

Ex:
Nz([state],"PA")

 

[JEB]

Thank you - that worked

 

[honda882000]

You are welcome For future reference, Access uses Null to indicate blank values, unlike Excel, which uses "". Your IIF statement can also work as follows:

IIF([state] Is Null,"PA", [state])

But Nz is made for this purpose.

 

[boblarson]

You are welcome For future reference, Access uses Null to indicate blank values, unlike Excel, which uses "".

Well, actually yes AND no. Access CAN store empty strings "" so if your field is not null but accepts empty strings then you might miss it with the NZ function.

This will capture both Null AND empty strings ""

IIF(Len([FieldName] & "")> 0, [FieldName], "PA")