Rounding problem

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

If you want the query to select neither value:

round([BTUH])<65000

To select both values,

round([BTUH])<=65000,

or, alternatively, truncate any fractional part, by:

int([BTUH])<65000

Kevin Sprinkel
-----Original Message-----
My query criteria is supposed to return records of <65000
Click to expand...
BTUH. When I have a record that is 64999.9, it isn't
picked up. However, when I have a record that is 64999.8,
it is. I've tried both data types single and double, but
neither corrects the problem. I'm not aware of a rounding
function that I can use in Access SQL. How can I get the
query to return the record having BTUH of 64999.9? Thanks
in advance.
 
See "When Access math doesn't add up":
http://www.fmsinc.com/tpapers/math/index.html

What happens if you change the Decimal Places property of the field to 3
(single) or 11 (double)? Note that this only affects what is displayed, not
what is actually stored. You may find some strange digits appear to the
right of the 9. But that doesn't explain why an Access query thinks that
64999.9 is not less than 65000.

The solution may be to use a fixed point numeric data type such as Currency
(4 decimal places) or Decimal.

Also, please post the SQL from your query - maybe there's something in the
way you have constructed it that is causing the failure.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Perico said:
I'm not sure what you mean: I don't want to select neither, nor to I want
Click to expand...
to select both. I want to set up my QUERY so that if the record value is
less than 65000 BTUH it will be returned. I have a record with 64999.9 that
should be returned but isn't.
 
Your idea of using decimal worked in returning the record I was trying to return. But as you pointed out, you apparently lose seeing fractional portion of the number in the display. I'll keep playing around with the data type / format combination and see if I can get the best of both worlds.
 
Ah. I suspect the problem is here:

AND nz(e.maxBTUH,0) > nz(tg.BTUH,0)

What is the data type of maxBTUH? Is this the field that has the 65000 in
it? Or, is it the IIF in the SELECT clause that is returning "S216" when
BTUH is 64999.9?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Perico said:
Here is the SQL, but it's a little involved. All other records returned
Click to expand...
appear correct so far in testing. I will now try your suggestions
SELECT tg.ID, tg.BTUH, tg.System, tg.Product, tg.Cooling, tg.EER, tg.SEER,
Click to expand...
tg.IPLV, tg.COP, tg.HSPF, tg.TXV,
IIf([BTUH]>=65000 And [BTUH]<135000,"S216",

IIf([BTUH]>=135000 And [BTUH]<=240000,"S217",IIf([BTUH]>240000 And
Click to expand...
[BTUH]<=760000,"S218",IIf([BTUH]>=24000 And [BTUH]<65000,IIf(([Product]="AC"
Or [Product]="HP") And ([System]="S" Or [System]="P"),IIf((([EER]>=12 Or
[SEER]>=14) And [HSPF]=8.5 And [TXV]=True),"S181",IIf((([EER]>=11.6 Or
[SEER]>=14) And [HSPF]=7.9 And [System]="P" And
[TXV]=False),"S182",IIf(([EER]>=11.6 Or [SEER]>=13) And ([HSPF]=8 And
[TXV]=True),"S214",IIf(([EER]>=11.3 Or [SEER]>=13) And ([HSPF]=7.6 And
[TXV]=False),"S215",IIf((([EER]>=11 Or [SEER]>=13) And [HSPF]=8 And
[TXV]=False),"S104",IIf((([EER]>=10.5 Or [SEER]>=12) And [seer]<13 And
[HSPF]=7.6 And [TXV]=False),"S105","NOT YET"))))))))))) AS Measure,
IIf([Measure] In ('S104','S105'),1,IIf([Measure] In
('S214','S215','S216','S217','S218'),2,IIf([Measure] In
('S181','S182'),3,99))) AS Tier INTO QUALIFIEDS
FROM SAMPLE_APPLICATION AS tg
WHERE ((((SELECT first("e. minEffID") FROM ac_min_eff as e WHERE
Click to expand...
nz(e.minBTUH,0) <=nz(tg.BTUH,0) AND nz(e.maxBTUH,0) > nz(tg.BTUH,0) AND
nz(e.system,"") = nz(tg.System,"") AND nz(e.Product,"") = nz(tg.Product,"")
AND nz(e.Cooling,"") = nz(tg.Cooling,"") AND nz(e.minEER,0) <=
nz(tg.EER,0) AND nz(e.minSEER,0) <= nz(tg.SEER,0) AND nz(e.IPLV,0) =
nz(tg.IPLV,0) AND nz(e.HSPF,0) = nz(tg.HSPF,0) AND e.TXV =
 
When I add an "=" to the subquery portion reading "AND nz(e.maxBTUH,0) > nz(tg.BTUH,0) ..." it picks up the record, therefore there was an inconsistancy between the main query and the subquery that I think was causing the problem. Thanks.
 
AHA! Glad you figured it out.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Perico said:
When I add an "=" to the subquery portion reading "AND nz(e.maxBTUH,0) >
Click to expand...
nz(tg.BTUH,0) ..." it picks up the record, therefore there was an
inconsistancy between the main query and the subquery that I think was
causing the problem. Thanks.
 
Back
Top