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 (http://camslivesexy.com/cam/xSexyAngel)

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.

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

Oops, typo. Thanks

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 (http://volcanovaporizer.net/)

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

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 :)

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 :)

Try this

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

If you don't understand it post back

brian

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?:eek:

Brian

Why is it we look for complicated answers first?:eek:


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