Last Omit Comma Question (2 Viewers)

[Lily]

Hello,

This is my last "omit comma" question I promise...but would anyone know how to omit the comma if the city field is blank, in the label wizard as it returns an =Trim expression. I tried adding the following but it did not work:

=Trim([City] & ", " & [State] & " " & [Zip]) & IIf(IsNull([City]),Null,"," & [State]" " & [Zip])

Thanks for your help and advice, it is much appreciated.

Lily

 

[pdx_man]

=Trim([City] & ", " & [State] & " " & [Zip]) & IIf(IsNull([City]),"","," & [State]" " & [Zip])

 

[Lily]

pdx_man,

Thank you for your help, but if I may ask when I paste that into the label design, it comes up "The expression you entered contains invalid syntax, you may have entered an operand without an operator." Would you happen to know what I'm doing wrong? Thanks again, so much, for your help.

Lily

 

[Rich]

=Iif (IsNull[City],Trim([State] & " " & [Zip]),Trim([City] & " " & "," &" "& [State] & " " & [Zip])

 

[Lily]

Rich,

I apologize as I am not too good at this, but if I delete =Trim([City]&","&[State]&" "&[Zip]) and paste in the above I still get the same error message...and if I leave in the original =Trim statement and then paste in the above, I get the same error message? Thank you, Rich, for your help and advice, it is much appreciated.

Lily

 

[Rich]

You are using a text box I assume in which case delete the original and add a new one from the tool box set the control source as posted.

 

[Lily]

Rich,

Thank you again, I will try that, and I very much appreciate your help.

Lily