Results 1 to 2 of 2
  1. #1
    Join Date
    May 2017
    Posts
    1

    Unanswered: Converting text to numbers and losing all negative values

    Hi,

    I've set up an access database and in one of the fields in my table the numbers are stored as text and negative numbers have a "-" character preceding them. When I change the field data type from text to numbers in the SQL, I lose the negative values from my output.

    ie. in my code below, negative numbers are not being picked up by the select statement - only positive numbers are being captured.

    -------------------------------------------------------
    SELECT val(nz([db].[value])) as myamount,
    from [db]
    -------------------------------------------------------

    Thanks in advance for any help you can offer.

    Kim

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    804
    Provided Answers: 2
    Hi

    Without going into why you have this situation etc., and assuming there is no better solution, you could try something like this
    Code:
    Val(IIf(Left([TextNum],1)="_",-1*Mid([TextNum],2),nz([TextNum]))) AS myamount
    This may be s sledge hammer to crack a nut!!

    MTB

Tags for this Thread

Posting Permissions

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