Is there an SQL syntax that will omit the numbers from an address field? For example, 123 Main Street or 12345 Main Street would display simply as "Main Street". I can't use a substring of the field since the amount of numbers in the address can vary. Thanks for any assistance.

[This message has been edited by mstorer (edited 01-03-2002).]

How about looking at the left most character of the string and if it is a space or a number then remove it. Repeat till you hit a letter.

Here is a [lazy] way of doing it. Sure that the purists will cringe, but at least it works!!

Function TxtAdd(MyStr as string)
Do
If IsNumeric(Left(MyStr, 1)) Or Left(MyStr, 1) = " " Then
MyStr = Right(MyStr, Len(MyStr) - 1)
Else
TxtAdd = MyStr
End
End If
Loop Until Len(MyStr) = 1
End Sub

Place this code in a module and then in the field of your query have, TextAdd: TxtAdd([FieldName])

HTH

[This message has been edited by Harry (edited 01-03-2002).]

[This message has been edited by Harry (edited 01-03-2002).]

I actually had to tackle this not two weeks ago. I put this in a separate module because I need to do this calculation in both reports and queries. Here's what worked for me, modify for your purposes:


Public Function BlockLocator(MyAddress As String)
Dim strHold As String
Dim intBreak, intLen As Integer
Dim IsApt As Boolean

intBreak = InStr(MyAddress, " ")

If (intBreak) Then
IsApt = IIf(InStr(MyAddress, ",") > 0, True, False)
intLen = IIf(IsApt, InStr(MyAddress, ",") - intBreak, Len(MyAddress) - intBreak + 1)

strHold = LTrim(Str(Int(Left(MyAddress, intBreak - 1) / 100))) & "00"
strHold = strHold & Mid(MyAddress, intBreak, intLen)

Else
'This only occurs if the address has no space in it
strHold = MyAddress
End If

BlockLocator = strHold
End Function


HTH,
David R (thanks to Jack Cowley and raskew for the original code)

Thanks Harry and David. The code worked fine. It got me out of a huge pinch. Thanks again.