Filter Records not Begginning with Numberic Characters

[Jeff]

My table has a Street address field.
I would like to filter out records that do not begin with a numeric Character.
Also if possible I would like to filter records that do not have a space
between the numeric characters and the alpha - Example: 123Street

 

[John Spencer]

I would try

WHERE StreetAddress Not Like "[0-9]*"
or StreetAddress Not Like "*[0-9] *"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

WHERE field LIKE "[0-9]*"

could work,

or

WHERE val(field) <> 0


(assuming no address is like 0 Elm Street )



About those starting with a number but without space after the number,
assuming the values have no trailing blank at the start of the string (and
that the number in an integer):


WHERE Val(field) <> 0 AND
" " <> Mid( field, len( " " & Val(field) ), 1)



Vanderghast, Access MVP