How do you show records that only have numbers?

[Guest]

I have a query that pulls up records that have alphanumeric and numerical
values, and my problem is that I only want to display the numerical values.
How do I do this?
ex. a12345
123456
b23456
456789

I only want the query to display the 123456, and 456789 values. Any help
would be greatly appreciated.

Also on a side note, is there a way to determine which field is causing a
text data type error?

Thanks

 

[Allen Browne]

In the Field row in query design, enter:
IsNumeric([Field1])
replacing Field1 with the name of your field.
In the Criteria row beneath this, enter:
True

You may not like the way IsNumeric() handles things.
For example, Access considers this to be a valid number:
-(98.76-e+21!)

Therefore, you might prefer to test if the value of the field is the same
after you convert it to a number and back to the string. Try this Criteria:
CStr(Val(Nz([Field1],"")))
replacing "Field1" with the name of your field.

It really is important to use the correct data types for your fields.

To answer your final questions, these steps will help you avoid data type
errors:
a) Use the correct data type for your field.
b) Use the correct delimiters around literal values in the Criteria of your
queries.
c) Declare your parameters, with the correct type.
d) Explicitly typecast any calculated fields.
e) Set the Format property of unbound controls on a form, as a way of
declaring their type.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

For your first issue have you tried the "IsNumeric()" function.
Syntax: IsNumeric([YourField])

As for your side issue, have you tried using field validation in your table
to prevent data type errors?

Hope it helps,
Regards,
Nick.

 

[Jamie Collins]

I have a query that pulls up records that have alphanumeric and numerical
values, and my problem is that I only want to display the numerical values.

This will return only rows with a numeric CustomerID (and should
provide a clue how to write the column/field level Validation Rule once
you have scrubbed the data):

SELECT *
FROM Customers
WHERE CustomerID NOT LIKE '%[!0-9]%'
AND CustomerID NOT LIKE '*[!0-9]*';

Jamie.

--