Results 1 to 5 of 5
  1. #1
    sephiroth2906 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    73

    Data in Excel needs to be currency, but negative amounts are strings formated as 123.45-

    I have a Access database I am creating to try an modernize financial data from an old system that is getting phased out. The people maintaining the system were only able (or willing) to provide me with a PDF of the data, which I have mostly finished turning into an Excel spreadsheet with the intent of importing into Access.



    The problem I am running into is that the negative values in the PDF, and thus Excel, are formatted with the minus sign after the value, so something like 123.45-

    In Excel,
    Code:
    =VALUE(LEFT(R71,6))*-1
    does work, but it of course would turn positive values negative and a different number of characters would require the formula to be changed.

    I am at a loss to determine a way to change all of these to currency without also affecting the positive numbers. I am also not sure if it would be easier to do this in Excel before importing it or if Access has a better way to handle this.

    Any ideas would be appreciated. Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use the Right() function to test if the last character is "-" and only multiply by -1 if so.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why put into Excel only to import into Access later - probably as un-normalized data?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    sephiroth2906 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    73
    Why put into Excel only to import into Access later - probably as un-normalized data?
    I didn't know that was an option, but after reading this post, results from attempts to dump the data from the PDF into Access were decidedly worse than the output in Excel. I do appreciate the suggestion though!

    I did manage to figure out a way to do this, but it ended up being a program written in AutoHotkey rather than a formula in Excel or Access. I know this is an Access forum, but I figure someone might stumble on this someday, so I thought I would post the base code. I just activate the top cell and it copies the last character onto the clipboard, checks to see if it is "-", if it is, it deletes it and adds it to the front of the data and then does it again on the cell below, and if not moves on to the next cell.

    Thanks!

    Code:
    +F12::
    Loop, 500
    {
    clipboard := ""
    SendInput, {F2}
    SendInput, +{Left}
    SendInput, ^c
    Clipwait
    
    clip1 = %clipboard%
    text = -
    
    If InStr(clip1, text)
    {
    SendInput, {Backspace}
    
    SendInput, ^{Left}
    SendInput, -
    SendInput, {Enter}
    
    }
    
    Else
    {
    SendInput, {Right}
    SendInput, {Enter}
    
    }
    }

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    As mentioned in post #2
    Code:
    =if(right(a1,1)="-",left(a1,len(a1)-1)*-1,a1)
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 1
    Last Post: 03-09-2016, 05:12 PM
  2. How to Get Data to Override Higher Amounts
    By ns8069 in forum Database Design
    Replies: 1
    Last Post: 01-28-2015, 02:46 PM
  3. export to excel - formated
    By webisti in forum Import/Export Data
    Replies: 2
    Last Post: 12-17-2012, 08:14 AM
  4. Summing Positive and Negative Currency
    By nweird in forum Reports
    Replies: 1
    Last Post: 07-22-2010, 10:05 AM
  5. Matching positive with negative amounts
    By cwert11 in forum Access
    Replies: 1
    Last Post: 09-29-2008, 12:26 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