Results 1 to 5 of 5
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Table values (currency/number) (2002 SP-2)

    I use my app to do all the calculations for billing and invoicing. The calculations for the amounts associated with bills and invoices is rather complex, and is accomplished by several calculated fields in a query. The end result was then used to print invoices and generate two reports (a list of invoices, and a list of bills associated with those invoices). I then performed the tedious task of re-entering all the same info (invoice and bill amounts) into Quickbooks (QB) for accounting purposes. I say was because I am now attempting to automate the process with an Access Add-In called Data Flow Management (DFM). (I will only address the invoice process here because the billing process is essentially the same). I use a make-table query to pass this information to QB via DFM.

    In my first attempt to pass this info everything looked fine and I was quite happy with the results, until I noticed that some of the values for both bills and invoices were off by $0.01 in the QB files. So, because the actual invoice that is mailed to the customer comes from my app, but payment entries are made in QB, there will sometimes be a discrepancy in the amounts (creating havoc with my accounting). I have contacted DFM support and they claim that their add-in passes the actual value to QB, but that QB completely ignores any value past the hundredths place holder.

    In the attachment you will see the Invoice number (also used to track the associated Bill), the Currency value (where the table property is set to currency), the Number value (where the table property is set to sng, std, 2), and the QB entry result regardless of the table property settings. As you can see, QB is interpreting Currency no differently than Sng, Std, 2; it is chopping off anything past the hundredth place holder (at least according to DFM). Historically, none of the values (invoice or bill amounts) would exceed $10,000.00

    Finally, the question. Is there a way to accomplish
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Table values (currency/number) (2002 SP-2)

    You must distinguish between the value stored in a table, and the value displayed on screen or printed on paper. The Currency data type displays only two decimals by default, but stores up to four decimals.

    You can use the Round function to round the results of calculations to 2 decimals, so that the displayed value and stored value are the same. Where your make-table query calculates expression, replace it by Round(expression,2)

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Table values (currency/number) (2002 SP-2)

    Hans:

    Thanks for your reply. So, then would:

    TotalBilledCalc: [SubTotalCalc]+Nz([StandbyTotaldolCalc])+Nz([DropTotaldolCalc])+Nz([FSCdolCalc])+Nz([curMiscCharge])

    Become:

    TotalBilledCalc: ROUND ([SubTotalCalc]+Nz([StandbyTotaldolCalc])+Nz([DropTotaldolCalc])+Nz([FSCdolCalc])+Nz([curMiscCharge]),2))
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Table values (currency/number) (2002 SP-2)

    I think there is one closing parenthesis ) too many, but apart from that, yes.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Table values (currency/number) (2002 SP-2)

    Hans:

    Sorry for the delay, I wanted to get all my testing done before replying.
    Yes, one too many closing parenthesis, but otherwise everything works perfectly now!

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>...why do your solutions always seem to take up 1/1,000th of the space my questions take up? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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