Split PostCode

[D Willett]

Hi Guys

I need to split a postcode which I can do by removing the last 3 characters but that sometimes leaves a space. The field is:

ContactPostcode

RawPostCode: Left([ContactPostcode],Len([ContactPostcode]-3))

TrimmedPostCode: Trim(Left([RawPostCode],Len([RawPostCode]-3)))

But these return #Error, can anyone see why?

Kind Regards

[HansV]

Move the closing parenthesis after -3 to after [ContactPostcode]:

RawPostCode: Left([ContactPostcode],Len([ContactPostcode])-3)

TrimmedPostCode: Trim(Left([RawPostCode],Len([RawPostCode])-3))

[D Willett]

Hmm, not quite the result I was after Hans?

ScreenHunter_097.jpg

[HansV]

Sorry, the latter should be

TrimmedPostCode: Trim(Left([ContactPostCode],Len([ContactPostCode])-3))

or

TrimmedPostCode: Trim([RawPostCode])

[HansV]

If you want to suppress the error value, use (for example)

RawPostCode: IIf(Nz([ContactPostCode])="",Null,Left([ContactPostcode],Len([ContactPostcode])-3))

TrimmedPostCode: IIf(Nz([ContactPostCode])="",Null,Trim(Left([ContactPostCode],Len([ContactPostCode])-3)))

[D Willett]

Just the ticket Hans, that's very useful.

Thanks once more.

Cheers

[macropod]

Try:
=LEFT([ContactPostCode]& " ",FIND(" ",[ContactPostCode])-1)
Since one of your codes lacks a space, that one will be output in full.

[HansV]

This is about Access, Paul...

[macropod]

Oops! Perhaps something along the lines of:
Split([ContactPostCode]& " "," ")(0)

[HansV]

You'd have to wrap that in a VBA function, for Split doesn't work when used directly in a query.

[macropod]

Fair enough - I don't work with Access much.