Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Amounts That Have Values In 3rd & 4th Decima (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following values in a table currency amount field

    5.1000
    6.7680
    7.0000
    8.3040
    9.5100

    Is there a way to query just the records that have a decimal value in the 3rd and 4th position as follows:?

    6.7680
    8.3040

    Thanks, John

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

    Re: Query Amounts That Have Values In 3rd & 4th Decima (a2k (9.0.6926) SP-

    Say that your field is named Amount.
    Create a query that includes Amount.
    Create a calculated column in your query

    Test: 100*[Amount]-Int(100*[Amount])

    Clear the Show check box for this column, and enter

    <>0

    (that is < followed by > followed by 0) in the Criteria line.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Amounts That Have Values In 3rd & 4th De

    The query with the following SQL should do the trick:
    <div style="width: 100%; background-color: #FFFFFF;">SELECT Table1.amount
    FROM Table1
    WHERE ((([amount]-Round([amount],2))<>0));</div hiblock>
    (adjust the table and field names where appropriate).

    The key function is <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Round([amount],2)</span hi>, which returns your amount rounded to 2 decimal places. Subtract this from the original amount to give anything in the 3rd or 4th decimal places, and filter it so you see just the non-zeros.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Amounts That Have Values In 3rd & 4th Decima (a2k (9.0.6926) SP-

    Thanks Hans and Wagger

    Worked OK

    I fell into the same trap as Leesha in Rounding up not wanted (2002) Post: <post#=485,074>post 485,074</post#>

    I have changed my form from:

    Me.curAmount = Nz(Me.curNetProfit, 0) * Nz(Me.curPrice, 0)
    To:
    Me.curAmount = Round(Nz(Me.curNetProfit, 0) * Nz(Me.curPrice, 0), 2)

    So new transactions entered are rounded OK to 2 decimal places

    My problem is the transactions that are already in the system that look like:

    10.5720
    2.6340
    6.7680
    8.3040
    5.6580
    30.0030

    This query seems to work rounding to to 2 decimal places

    <pre>UPDATE qrytbl_RevTran_MasterDetail_All
    SET qrytbl_RevTran_MasterDetail_All.curAmount = Round(Nz([curAmount],0)*1,2)
    WHERE (((100*[curAmount]-Int(100*[curAmount]))<>0));
    </pre>


    Is the above a Update Query going to be reliable in all situations to change from to:

    10.57
    2.63
    6.77
    8.30
    5.66
    30.00

    Thanks, John

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

    Re: Query Amounts That Have Values In 3rd & 4th Decima (a2k (9.0.6926) SP-

    That should work the way you want it to.

Posting Permissions

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