How do I refer to part of a concatenated field?

[Irmgard Wesselb]

Hi,

I have a mailing label report with a text box (txtAddress) containing:
=[Suburb] & " " & [State]&" "&[PostCode]
which works fine.
Most postcodes in the database have 4 digits, but a few of the addresses only have a 0 (zero) in the Postcode field.
My question: is it possible to suppress this 0 when printing, and if so, how is it done?

The following code, attached to the On Format Event of the detail section, works nicely when the PostCode field is on its own:
---
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Me.PostCode = 0) Then
Me.PostCode.Visible = False
Else
Me.PostCode.Visible = True
End If
End Sub
---

However, I would prefer to keep all three fields (Suburb, State, PostCode) concatenated, and the PostCode not showing if it is 0.

Any hint would be greatly appreciated.

Thanks and regards,
Irmgard (Irma) Wesselbaum

 

[mhartman]

Hello:
Update your control with the following:
'
=[Suburb] & " " & [State]&" "& IIf([PostCode]<>0,[PostCode],"")

Regards
Mark

 

[Irmgard Wesselb]

Hallo Mark,

Thanks a million for the fast response. I have changed the code as per your suggestion, and it works perfectly!

Regards,

 

[mhartman]

Hello: You are most welcome!