Access Query, convert text to number

Posted on 2008-11-18
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
    LVL 38

    Accepted Solution

    In access it is CInt(), CLng(), CDbl() and others. Check the Access help for the full list.
    LVL 65

    Expert Comment

    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.
    Glad to be of assistance. May all your days get brighter and brighter.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now