Selecting the numeric section a field 2

[tpowers]

I have a location field in my table, and in this field I need to pull out only the numeric section of the field.

I was wondering if anyone could help me with the code?
I have been trying to do this forever but I just can not get the query or code right.

the numeric section of the location is on the left side of the string just like any other address, if that helps any.

Thank you in advance,

TPowers

 

[PaulBricker]

If your selection is
123ABCD
use this.
Val("123ABC)
It will return
123
You would use Val([FieldName])

Paul

 

[tpowers]

OK, sounds great, I have one more thing to ask you, what if there is a space between the numbers and text will this still work? Or what if there is a letter in front of the number IE: N 123 main st, how can I tell?

 

[PaulBricker]

The Val function will work if there is a space between the numeric portion and the text portion. It will not work if the string starts with a text value like
N 123 main st. We could write a function that loops thru the string and looks for the first instantance of a numeric value and then returns that if you want. Let me know and I can write that for you.

paul

 

[PWise]

val(mid(trim(location]),instr(trim([location]),chr(32)+1,99))
will return 123 from "N 123 main st"

 

[PWise]

Val(StrReverse(Val(StrReverse([location]))))
will return 123 from "n123" in a2k

 

[tpowers]

Thank you to both of you guys