Formatting output in an iif statement

[JackieEVSC]

I am attempting to pull in the value of a field. If the value is null, I want the result to be "n/a". If there is a value in that field (CopyBarcode), I want the last seven digits of that value to populate.




This is the code I am trying:


AssetNum: IIf(([Admin_Copy]![CopyBarcode])="", "n/a", "Right([Admin_Copy]![CopyBarcode],7)"


When I enter this, I get an this error: "The expression you entered is missing closing parenthesis, bracket (]), or vertical bar(|).


I have tried a variety of things to correct this, but I keep getting the same error, regardless of what I try. As usual, I'm sure this isn't a difficult thing to do, but I'm stuck!


Thanks for any help you can give me.

[aytee111]

Remove the " before Right and at the end. Remove the set of () around barcode. Add a ) at the end.

[JackieEVSC]

I tried to follow your advice, but I'm missing something.

The following give me an error "The expression you entered contains invalid syntax":

1 - AssetNum: IIf(([Admin_Copy]![CopyBarcode])="","n/a",Right[Admin_Copy]![CopyBarcode],7)
2 - AssetNum: IIf(([Admin_Copy]![CopyBarcode])="","n/a",Right([Admin_Copy]![CopyBarcode]),7)
3 - AssetNum: IIf([Admin_Copy]![CopyBarcode]="","n/a",Right[Admin_Copy]![CopyBarcode],7)
4 - AssetNum: IIf([Admin_Copy]![CopyBarcode]="","n/a",Right[Admin_Copy]![CopyBarcode],7)

The following doesn't give me an error, but the field is empty after the query runs:


AssetNum: IIf(([Admin_Copy]![CopyBarcode])="", "n/a", Right([Admin_Copy]![CopyBarcode],7))

What am I doing wrong?

[John_G]

Be careful about testing for "" (empty string) in a form text box. An blank text box on a form rarely will contain an empty string - it is far more likely to be Null. You can test for both possibilities with:

IIf(nz([Admin_Copy]![CopyBarcode],"") = "", "n/a", Right([Admin_Copy]![CopyBarcode],7))

If [Admin_Copy]![CopyBarcode] is Null, the Nz returns an empty string, otherwise it returns the existing value in [Admin_Copy]![CopyBarcode] (which might be an empty string)

[aytee111]

The first 4 are wrong, no ending ). Remove those () around barcode.

Barcode might also be null: IIf(IsNull([Admin_Copy]![CopyBarcode) OR [Admin_Copy]![CopyBarcode]="",

[MatthewGrace]

I think it's probably:
AssetNum: IIf(IsNull([Admin_Copy]![CopyBarcode]) OR [Admin_Copy]![CopyBarcode]="","n/a", Right([Admin_Copy]![CopyBarcode],7))

Hope I'm right! Good luck!