Results 1 to 5 of 5
  1. #1
    JesterGrafix is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    8

    Change Negative numbers to Positive numbers in my query

    Hey all. I am sorry if this has been asked before. I did search and everything I have tried has not worked. Here is my issue.



    I have created a Database. The database only has 1 table that I have linked to (this table is updated everyday). The table has 2 columns that when we run the report out of our work system, they are negative numbers. I need to make these be Positive numbers when I run the query. I have changed it to show one of the columns as currency so it is putting them in parentheses () because they are negative.

    I went into the query, went into design view, chose Update and then tried multiple ways of doing the Update To field, each time it has not worked. For instance, I tried going into the Expression builder and entering Abs([Quantity]*-1), when I do this, if I click back on Select (instead of Update) and then click Run, it still gives me negative numbers. If I leave it on Update and then click run it tells me that the Operation must use an updateable query. Each different way that I have entered it I get the same result.

    I feel like I am missing something simple, I just cannot figure out what is happening.

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Abs([Quantity]*-1) should resolve to positive number. Are you sure it's not Abs([Quantity])*-1 ?
    In any case if using ABS, you don't need to multiply by -1, just ABS([Quantity])

  3. #3
    JesterGrafix is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    8
    Hi, thanks for the reply. I did verify that I had it typed in the way you said. I also changed it to what you said would work and I am still getting the same error message when clicking run with Update selected. if I run with Select selected, it runs but still has negatives..

    Click image for larger version. 

Name:	Setup.png 
Views:	15 
Size:	7.2 KB 
ID:	43077

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Add a new field to the query and call it PosQuan:Abs(Quantity)
    No criteria

    There's no particular reason to update the table. Just use the query in its place.
    Last edited by davegri; 09-29-2020 at 09:48 AM. Reason: sp

  5. #5
    JesterGrafix is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2018
    Posts
    8
    Awesome, Thanks so much! Got exactly what i needed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-07-2015, 03:49 PM
  2. Replies: 2
    Last Post: 01-13-2015, 02:34 PM
  3. Replies: 1
    Last Post: 10-22-2014, 01:28 PM
  4. Replies: 7
    Last Post: 06-29-2013, 06:01 PM
  5. Replies: 8
    Last Post: 06-13-2013, 06:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums