How to Get Rid of an extra space

[CP611]

I currently have a make table query which contains the formula ADDRESSLINE1: StrConv([Address],3) & (Chr(13)+Chr(10)+StrConv([Address2],3)) so that it combined Address and Address2 into one cell, making sure to convert it into capitals and put Address2 onto a new line. This works fine however when there is nothing in Address2 it still puts in an enter. Therefore, when it is imported into a CRM system and mail merges are created it will look like this:



Mr Smith
20 Smith Lane

London
SW19 8DF

Is there a way to remove the extra enter line if the Address2 is blank?

Thanks (very new to this so not really sure what I'm doing!)

[JoeM]

Check to see if there is a value in Address2 before adding it, i.e.

ADDRESSLINE1: StrConv([Address],3) & IIF([Address2]<>"",(Chr(13)+Chr(10)+StrConv([Address2],3)),"")

[CP611]

That's great thank you!

Another one... is there a way to format postcodes so they come out correctly? Some of them would have been entered correctly but others may not have a space in. At the moment I've got:
POSTCODE: StrConv([Postal Code],1)

[JoeM]

How are they not coming out correctly?
Can you post a few examples of ones that work, and ones that do not?

[CP611]

Hi,
Some will go in as SW19 2JI for example and come out correctly. Others will go in as LS192BH and come out the same, i.e. without the space in, whereas I would want them to come out as LS19 2BH, but obviously not every postcode is the same, I believe they all have 3 letters in the second half but some have just G1 in the first half for example.

[JoeM]

So, do they allow follow the same rule, where it should be 4 characters, followed by 1 space, followed by three characters?
And the issue is that some already have the space in there, and others don't?

If so, this should do it:

POSTCODE: Left([Postal Code],4) & " " & Right([Postal Code],3)

[orange]

Try this where x represents your postal code

IIf(InStr(x, " ") > 0, x, Left(x, 4) & " " & Mid(x, 5))

[CP611]

The one with x in doesn't seem to work, says invalid syntax error
The issue is that some already have a space in and others don't, however the postcodes are not always the same, some are XX XXX some are XXX XXX some are XXXX XXX.
E.g. Input:
SW16 8PQ, SW18TH, SW3 4EW, SW323KL would be: SW16 8PQ, SW1 8TH, SW3 4EW, SW32 3KL. I.e. always has 3 characters in the second section.

Thanks

[JoeM]

Here is one way:

POSTCODE: Left([Postal Code],Len(Replace([Postal Code]," ",""))-3) & " " & Right([Postal Code],3)

[CP611]

that seems to work! Thanks

[JoeM]

You are welcome.

[isladogs]

You might be interested in this info about the structure of UK postcodes which is supplied with one of my commercial apps.

[accesstos]

Try one more:

PostalCode: Replace([Postal Code]," ","")

and set the format property to:

>&&&& &&&