Number is changed from table to query. Why? 2

[wendyp]

Hi all - weird stuff.

I have a table of tax rates
Maricopa Phoenix 0 0.063 0.018 0.35

I have a function that gets the tax rate based on county, city & zip. There are NO calculations performed in this function - just decision on which row to return.

In a query, I am using that function to return the city rate (because I can't figure out a way to return an array and populate multiple columns of the query - but that would be anoter post).

In the query I get:
Phoenix AZ 85086 Maricopa 1.79999992251396E-02

What happened to my accurate 0.018???

The function is basically:
***********************************************************
' Find a match for county, city, zip
strSQL = "SELECT * FROM tblAZTaxRates" & _
" WHERE ((tblAZTaxRates.strCity)='" & varCity & "'" & _
" AND ((tblAZTaxRates.strZip)='' OR isnull(tblAZTaxRates.strZip))" & _
" AND (tblAZTaxRates.strCounty)='" & varCounty & "'" & _
" AND (tblAZTaxRates.datEffective)<=#" & Date & "#)" & _
" ORDER BY (tblAZTaxRates.datEffective) DESC;"

'debug.print strSQL

rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
With rs
If Not (.EOF Or .BOF) Then

.MoveFirst
found = True

varCountyTaxRate = .Fields("numCountyTaxRate")
varStateTaxRate = .Fields("numStateTaxRate")
varCityTaxRate = .Fields("numCityTaxRate")
varDiscountRate = .Fields("numDiscountRate")

End If
.Close
End With

End If
***********************************************************

Thanks for any insight on this.
/Wendy
PS: I know I could round to 3 decimals and get what I need - for this example, but occassionally we do need to go out to 4 places, so I might run into a problem there.

 

[Golom]

The data type of the TaxRate field in the table is probably a Double and that is a floating point type of numeric representation. Not every decimal value can be exactly represented by float data types. I would suggest changing this to a Currency or a Decimal data type, both of which are scaled integer data types and will store exact representations of values within their defined range.

 

[PHV]

Use Currency or Decimal types instead of Single or Double.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wendyp]

Thank YOU both!! That was it - I changed the data type to currency and the format to percent - I had to put the decimal places at 6 - auto wouldn't do it.

NOW - I have another problem with these same numbers.
I have 4 fields which are currency, with percent format

I add the first 3 fields together on the form
County: 0.0000%
State: 6.6000%
City: 1.5000%

I display the:
Total: 8.1000%

Then in the last box, I take a 35% discount off this rate. The formula I use is:

=([numCountyTaxRate]+[numStateTaxRate]+[numCityTaxRate])*(1-[numDiscountRate])

The answer I get is: 5.2600%

If you use a calculator, then answer is: 0.05265

Why am I losing that last 1,000th of a percent?

/Wendy

 

[mhartman1]

Hello:

I have done the calculation as you state, however I get the correct answer you ask. All my text boxes are of the FORMAT = General Number, and DECIMAL PLACES = Auto.
Regards
Mark

 

[Golom]

You are probably losing the last digit because currency data types are limited to 4 places after the decimal. If you need more than that then convert the fields to a Decimal Data Type that supports up to 28 digits after the decimal place.

You could also force Decimal calculations with

=(cDec([numCountyTaxRate])+(cDec([numStateTaxRate])+
  cDec([numCityTaxRate]))*cDec((1-[numDiscountRate]))