Conatenate fields and also remove blanks

[Rubijon]

Hello,

First off thanks for taking the time to read this and bare with me as I'm relativly new to access.

So, how would I go about concatenating multiple fields for a report and also leave out empty fields?

I would like to concatenate an address

The fields are
SiteAddress (text)
SiteSuburb (text)
SiteState (number - combo box)
SitePostcode (number)

So far I have come up with =[CentreAddress] & ", " & [CentreAddressSuburb] & ", " & [CentreAddressState] & ", " & [CentreAddressPostcode]

Which gives me: 55 River Street, Maclean, 1, 2463
How do I make the 1 = NSW?

Also if the suburb is blank it will look like this
55 River Street,, 1, 2463



How would I go about fixing both those issues?

Thanks for your time.

[Stingaway]

Ok, first off if this is a query, then you could say IIF([SiteState] = 1, "NSW", [SiteState]) - or replace that final [SiteState] with whatever you want to be there if the number is NOT a 1.

If it is a report, in your query you should reference a table for SiteStates, link it, and pull down the corresponding field for the name of the site state instead of the number.

You are on the right track with concatenations, if you want the blanks to disappear, try putting a trim statement on the fields.

Here is an example of one I use for an address field on a report:

=(Trim([Fname]) & " " & Trim([Lname]) & Chr(13) & Chr(10)) & ([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) & ([City]) & " , " & ([State]) & " " & ([Zip])

The Chr(13) and the Chr(10) give me a new line if I remember correctly...

Hope this helps.

[RuralGuy]

You might also tell us the SQL for the RowSource of the SiteState ComboBox and maybe there is another option for that element.

[Rubijon]

=(Trim([Fname]) & " " & Trim([Lname]) & Chr(13) & Chr(10)) & ([Address1]+Chr(13)+Chr(10)) & ([Address2]+Chr(13)+Chr(10)) & ([City]) & " , " & ([State]) & " " & ([Zip])

The Chr(13) and the Chr(10) give me a new line if I remember correctly...

Hope this helps.

Thank you both for replying.

This works a treat! Thanks so much.