select first free letters of the postcode only

[mattaus]

Hi in my ms access query i have a field called 'postcode',

i only want to select the first 3 digits only and ignore the rest?

also what would the syntax be if i wanted to selct the last 3 digits only?

I thought it may had been the trim function but im not sure now
Thanks

mattaus

 

[DCrake]

You can use the Left(postcode,3) or Right(Postcode,3) to get what you want.

 

[mattaus]

Thank alot!!!

 

[JANR]

Use Left or Right Function to extract what you want.

PostcodeRev: Left([Postcode],3) - first 3 characters
PostcodeRev2: Right([Postcode],3) -> Last 3 characters

JR

 

[mattaus]

Thanks again

 

[gemma-the-husky]

arent you more likely to want to select digits up to or after the space

eg in nottingham NN1 is not going to be very useful
you want NN12, or NN14

the position of the space is instr(postcode," ")

so upto the space is

left(postcode,instr(postcode," ")-1)

after the space is

mid(postcode,instr(postcode," ")+1)

---------
this is not an issue with well formed postcodes, but wouldn't give the right split with postcodes without a space

 

[mattaus]

Ahh Thankyou !!

 

[Atomic Shrimp]

Is this a UK postcode? Just wanted to point out that the codes are divided into two parts, the first of which may be two, three OR four digits - selecting just the first three digits would (for example, here in Portsmouth) group addresses in outlying areas such as Fareham (PO16) and Chichester (PO19) all together with inner-city addresses in PO1.

Some cities only have one letter as the area prefix, so the first portion might be only two digits (such as B1 for Birmingham).

It's further complicated by the fact that some people type postcodes in with a space separating the two parts and others type it as a continuous string.

The way to get the first portion of a UK postcode from a string is to take everything up to the end of the first block of numeric characters.

 

[mattaus]

ahh thanks mike,


yes it is a uk postcode, what would be the syntax to do that?

 

[Atomic Shrimp]

Actually, it's worse than I thought - the second part of the postcode starts with a number, so the first part needs to stop at the penultimate numeric character after position 2.

Because the second portion of the postcode is always three digits, it might be easier to say that the first bit is everything that isn't the last bit (i.e. except the last three non-blank digits)

This function should do it:

Public Function PostcodePart(postcode As String, SelectPart As Integer) As String
Dim postcodetemp As String
postcodetemp = Replace(postcode, " ", "")  'ignore spaces
Select Case SelectPart
Case Is = 1
'return the first part of the postcode
PostcodePart = Left(postcodetemp, Len(postcodetemp) - 3)
Case Is = 2
'return the last part of the postcode
PostcodePart = Right(postcodetemp, 3)
Case Else
'return nothing
End Select
End Function

To use the function, paste it into a new module in your database.

Then use it in queries, etc, as follows:

PostcodePart([YourPostcodeField],1)

or

PostcodePart([YourPostcodeField],2)

-it should return the first or second part of the postcode, respectively.

 

[gemma-the-husky]

assume the postcode is well formed -
eg a code like L11 L 03X just isnt a postcode

if the code has a single space assume theres no problem
just split it into left part, and right part

if there is no space then a code has to be between 5 and 7 characters, and (I think) the right part is always 3 characters, and the left part is the remainder.

I hope that helps

----------
this is over the top - but you could always try and read the lat/long from google for a post code - which would tell you whether it was a true code or not.

 

[mattaus]

thank you all-this is great