house numbers from addresses (1 Viewer)

[deekras]

i need to take the house numbers from the address field. if it is a PO Box or the address has a hyphen or letter, i want the field to be blank.

i tried this:
addr: Left([address],InStr([address]," ")-1)

that gave me whatever was put in before the first space. it did include the PO and addresses like 19A.

then I wanted to determine if it was a PO Box
so i determined what the first character was.
A: Left([addr],1)

then i ran a query on that, asking to display [addr] if [A}<="9" And >="1".

So if the address had a PO Box it would show up blank, but that doesn't solve the hyphen and letter problem. can i somehow tell it only to display if the [addr] if it contains only numeric characters? i suppose i could write a function, but i have no idea how to write it.

 

[Travis]

If all you want is the Number (No alpha or symbol characters) you can use this:

Val([Address])

What this will do is only read the first numbers and return them:

Example:

Val("1410 W. Abby") = 1410
Val("P.O. Box 112") = 0
Val("19A") = 19

HTH

 

[deekras]

what about 19-34?
the val("19-34") gives me 19. i need it to be blank or 1934, not just 19.

 

[pcs]

deekras

i just gotta ask...why do you need to do this?

al

 

[joeyreyma]

here's a function:

Public Function GetNumericInString(strText As String) As String

'extracts all numeric from the string
Dim intLoop As Integer
Dim strResult As String

If Not (IsNull(strText)) Or strText <> vbNullString Then
If InStr(1, strText, "P.O.", vbTextCompare) Or InStr(1, strText, "PO", vbTextCompare) Then
strResult = vbNullString
Else
strResult = vbNullString
For intLoop = 1 To Len(strText)
If IsNumeric(Mid$(strText, intLoop, 1)) Then _
strResult = strResult & Mid$(strText, intLoop, 1)
Next
End If
End If

GetNumericInString = strResult

End Function

 

[deekras]

i need this because i am sending a file to a program that uses house numbers to verify the account number i am debitting from. the program only accepts numeric characters in that field. it is better to leave it blank than to put in letters. (it is only one of the verifications - there are others).