Extracting Numeric data only from a text field

[CarrieR]

Field name is serial Number, data type is text. Problem is some have numeric value only, some numeric combined with text. How would I extract only numeric values in a query? Text is in the field in various places, 1st, middle of 9 digits, etc.

 

[SkipVought]

I copied and pasted the function I just posted and used it sucessfully in a query on a TEXT FIELD!

Does NOT strip out leading zeros.

Does NOT error.

What is the Field Type of ModemSerial#

???

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[EBGreen]

Here are some examples of numbers I don't want: 5397M, 4287M, 64554M000123. The ones I do want, from the same field would be as such: 992041234, 042134587. The actual field name is: Modem Serial #.

From this I think it would be helpful for CarrieR to restate the problem along with complete examples of data from the field, what data needs to be returned from a function given a certain input, the field type, and what code is currently being used and how it is implemented. Something of a reset if you will.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[SkipVought]

I took for granted that CarrieR knows the name of the field.

If the field name is Modem Serial #, then
[tt]
JustNumbers([Modem Serial #])
[/tt]


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[CarrieR]

Skip

Got it working, thanks for your help. Only one issue left, it leaves a blank line where it is stripping out data that is excluded. Is there any way to just show only the numeric data?

 

[PHV]

In the query grid:[tt]
field NumOnly: getNum([Modem Serial #])
criteria Not Null[/tt]
provided you use my function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CarrieR]

Skip and everybody else:

Finally got this to work, used the last function suggested by Skip. To eliminate blank lines in the query, had to use not "" in criteria (am using Access 97). Thank you everybody for your help in this. Someday I hope I can do 1/10th of what you guys do.

 

[SkipVought]

Just eliminate these two lines of code

  Else
     getNum = ""

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}