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

1. ## 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. ## 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. ## 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.

4. ## 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. ## 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
•