Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access Query, convert text to number

Posted on 2008-11-18
Medium Priority
Last Modified: 2012-05-05
I am creating a Microsoft Access 2003 query.  In the query I would like to convert a text field to a number.  If I try to use the CONVERT function, I receive the error: "Undefined function CONVERT in expression".  The data is from a linked SQL Server 2005 database table.  The original SQL server database table can not be altered.

How to I convert a text field to a number type in an Access 2003 query?
Question by:nulad
  • 2
LVL 38

Accepted Solution

Jim P. earned 2000 total points
ID: 22988500
In access it is CInt(), CLng(), CDbl() and others. Check the Access help for the full list.
LVL 65

Expert Comment

ID: 22988921
You could use VAL as well. Might be worth wrapping with NZ as well just in case you have nulls. eg  select val (nz (myfield,"0")) as mynumber
LVL 38

Expert Comment

by:Jim P.
ID: 22989181
Glad to be of assistance. May all your days get brighter and brighter.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

578 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