return only numeric 1

[dingleberry]

Should be simple...?

Model Num
254973
254970
TX_serial
RTX_serial

I want to query the above and return only numeric? Any ideas?

Thanks,

 

[JerryKlmns]

SELECT *
FROM yourTable
WHERE IsNumeric([Model Num])=True;

 

[HarleyQuinn]

A small caveat to Jerry's post, while with your data IsNumeric() should cause no problems, it will return True with some mathematical notation.

With the data:
Table1

Field1

1
2
3
6e2

.g.

Select *
From Table1
Where IsNumeric(Field1)

Will return all of the rows including 6e2.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

Ok HarleyQuinn,

I 've seen it in onother thread and here it goes

SELECT *
FROM yourTable
WHERE IsNumeric([Model Num] & "e0" )=True AND
[Model Num] Is Not Null;

No scientific numbers returned

 

[HarleyQuinn]

Sorry, the query should have read:

Select *
From Table1
Where IsNumeric(Field1)=TRUE;

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

Too much pepper/salt ....

 

[HarleyQuinn]

Jerry,

Spot on, there are work-arounds to get it to return only on numbers. I was only pointing out to the OP what might have happened if they had experienced any unexpected results.

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Jerry -

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jrbarnett]

Wouldn't

SELECT *
FROM yourTable
WHERE [Model Num] Like '[0-9]*'

be quicker as you're not calling a VBA function from your code?

John

 

[HarleyQuinn]

John, would that not just return every record that starts with a number regardless of what's in the rest of the field?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jrbarnett]

True, but given the data set that dingleberry gave, it would do the job.

John

 

[dingleberry]

I think I love you people.

 

[PHV]

And what about this ?
WHERE Not ([Model Num] Like '*[a-z]*')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

dingleberry

We love it more!