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

x
?
Solved

Access Query, convert text to number

Posted on 2008-11-18
3
Medium Priority
?
4,286 Views
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?
0
Comment
Question by:nulad
  • 2
3 Comments
 
LVL 38

Accepted Solution

by:
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.
0
 
LVL 65

Expert Comment

by:rockiroads
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
0
 
LVL 38

Expert Comment

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

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