Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Data Type in Query (Access 2003 / 2007)

    I have several Access front ends that extract data from SQL Server tables. In SQL Server, customer and transaction numbers are big integers, which Access treats as text.

    Is there a way to change the data type within a query - some kind of format command? For example, I use Double for the transaction number. Is there a way, in a make table query for example, to tell Access to make that field Double? Does it have to be done in the SQL design window?

    Currently, I'm using mainly append queries to get around the problem, but am wondering if it's possible to change the data type in the query. It would be handy for doing select queries where all I want is a quick list of transactions for a given customer, for example.

    Thanks for any suggestions you have.

    Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Data Type in Query (Access 2003 / 2007)

    You can use a calculated column in the query:
    <code>
    NumValue: CDbl([TextValue])
    </code>
    This will transform the text field TextValue to a number (double precision) field NumValue. If you want to give the number field the same name as the text field, you must do it like this to avoid confusion:
    <code>
    MyField: CDbl(<!t>[TableName].[MyField])
    </code>
    Otherwise you create a circular reference.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Data Type in Query (Access 2003 / 2007)

    Thanks, Hans.

    It worked perfectly.

    I appreciate your quick response.

    Nancy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •