SQL test if field is numeric

Posted on 2013-09-03
Medium Priority
Last Modified: 2013-09-07
guys how do i test if a field value is numeric using an SQL way?

i can simply do isnumeric but that isn't very fast cause it's calling VBA functions.

thanks guys!! = ))
Question by:developingprogrammer

Expert Comment

ID: 39462595
How about a regex..?


SELECT * FROM myTable WHERE col1 REGEXP '[0-9]+';

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 39462675
that is Mysql, the question is MS Access, which doesn't have REGEXP.

as I wrote in your other question (http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28230107.html#a39462672), the isnumeric function used in your SQL is "not the same thing" as in the VBA, though they do the same thing.

and I would indeed recommend to use that one in your SQL, anything else will just be more complex.
LVL 53

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 total points
ID: 39462686
You can try with:

[Field] = Val([Field])

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Author Comment

ID: 39465844
i see cool thanks angelIII and gustav!

hrmm i think all my questions lead to the same solution source - which is where do i get the documentation for all the Access SQL functions? i can find the documentation for T-SQL but not for Access-SQL and i think they're slightly diffferent

Author Comment

ID: 39472528
guy the question in my previous comment is pending an conclusion from my other questions as well. once i get the conclusion from the other questions i'll post it here too. thanks!! = ))

Author Comment

ID: 39473926
ok guys the conclusion from everyone who's participated in all my other questions linked to this topic is....... TA DA! THERE IS NO SPECIFIC DOCUMENTATION FOR JET SQL FUNCTIONS! so make merry and be happy!! hahaha = ))

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question