Remove all Numeric from Post Code

[mosh]

Hi All,

I need help on a query for a post code lookup. I need to design a query whereby I only need the first part of the post code before the numeric digits start. So for example, if the post code is LE1 9PJ, it will only bring back LE.

Can anyone please help?

Thanks,
________
xSexyAngel live

 

[vbaInet]

What you need are two functions, Len() and InStr(). Have a quick look at them and if you get stuck with syntax please post back.

 

[Brianwarnock]

What you need are two functions, Len() and InStr(). Have a quick look at them and if you get stuck with syntax please post back.

Left function not Len

Brian

 

[vbaInet]

Oops, typo. Thanks

 

[mosh]

all,

i have the following:

left([Post_Town],Instr([Post_Town]

But i'm missing something, to identify the numeric value and then -1?
________
Vaporizer volcano

 

[Brianwarnock]

yes, its not that simple is it as there can be 1 or 2 alpha followed by 1 or 2 numeric before the space, and you only want the alpha.

Hmm thinking cap time

Brian

 

[vbaInet]

Ah, yes that's right. You also need the IsNumeric() function which checks a string for numeric characters. Have a look and see what you can come up with

 

[vbaInet]

Would something like this work?

Public Function ReturnLenPostcode() As String
    Dim i As Long, strPostcode As String, lenPostcode As Long
    
    lenPostcode = Len([Post_Town])
    strPostcode = ""
    
    If lenPostcode > 0 Then
        For i = 1 To lenPostcode
            If IsNumeric(Mid([Post_Town], i, 1)) = False Then
                strPostcode = strPostcode & Mid([Post_Town], i, 1)
            Else
                Exit For
            End If
        Next
    End If
    ReturnLenPostcode = strPostcode
End Function

It's untested but it compiles, give it a go.

Brian, what do you think?

Posted at the same time twice lol

 

[Brianwarnock]

Try this

pcalpha: IIf(Mid([pcode],2,1) Like "#",Left([pcode],1),Left([pcode],2))

If you don't understand it post back

brian

 

[Brianwarnock]

Brian, what do you think?

I was about to write a function, in fact had started when I realised that we were over complicating the issue, if the 2nd character is numeric the n we have 1 alpha else 2.

Why is it we look for complicated answers first?

Brian

 

[vbaInet]

Why is it we look for complicated answers first?

Haha! I guess that's just the way it goes and then you optimise (which takes me a while to realise )