Results 1 to 3 of 3

Thread: Text to Integer

  1. #1
    Join Date
    Mar 2005
    Location
    Wellington, New Zealand.
    Posts
    28

    Unanswered: Text to Integer

    Hi

    I am trying to SUM a text field, I think I need to convert text to integer using CAST, I understand that this cannot be done in access, unless you convert to varchar first, I am trying this and it will not work???

    "SELECT SUM(CAST(CAST(qtyA AS varchar(40))AS CINT)) AS qtyAcount FROM tbl_order"

    any ideas?

  2. #2
    Join Date
    Mar 2005
    Location
    Wellington, New Zealand.
    Posts
    28

    All Fixed

    Hi

    I tried this and it works fine when I remove the nulls:

    "SELECT SUM(Cint(qtyA)) AS qtyAcount FROM tbl_order"

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access vba has a wide range of conversion functions which allow you to change virtually any legitimate item to another datatype
    these include CINT, CLNG, CSNG,CDBL,CSTR and so on. mind you access also often does a pretty good job of doing the conversion all by itself if you assign a string to a number.. but you don't have that control, nor that granularity that most anal developers want if you do it that way.

    if you are converting text to a number you may want to check if its a number first, i forget which but I think its if (is_number(<mystrvariable>))

    the cint squizoid suggests you use is in fact part of VBA in access, and it is that function you are using int he SQl statement.

    mind you it does beg the question why yu are storing numbers as text

Posting Permissions

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