Unwanted rounding ( I think)

[Guest]

I am calculating seniority. I start out by using a datedif command as follows.

Seniority: DateDiff("ww",[Hired Date],#3/31/2007#).

This gives me the number of weeks of seniority. I then divide the result
by 52 to get years. So far everything is fine. Then I do a table lookup
based on the 1st number of years to get a multiplier factor. The table has
values from 0 to 4. So if anyone is under 1 year their multiplier is
different from someone who has been employed for more years. The table
values are 20 for 0-1 yrs; 40 for 1-2 yrs etc.

Percnt years: IIf([years] Is Null,'',(DLookUp("percentage","tblprcnt","yrs
=" & Left([Years],1))))

This works great except for 1 rotten person. This person hired in 3/5/07.
Their seniority comes back with 0.05769.... and the table look up does not
equate to 0. What is wrong?

 

[Guest]

Bunky,

I'm not sure if you wanted rounded or not.

If you want it rounded then place your IIf-stantement after a Cint being:
Percnt years: Cint(IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

and if not rounded make it a double: Cdbl(Percnt years: IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

Maurice

 

[Guest]

Maurice,

I tried both and got an error on the calculation.

I am simply wanting Access to look at a number of 0.05769, match it in a
DLookup to the '0' in the table. If the number is 0.1...., the matchup
occurs but values less than .1 do not get a valid match. I thought it was a
rounding problem but now I do not think so..... Ideas?

Bunky,

I'm not sure if you wanted rounded or not.

If you want it rounded then place your IIf-stantement after a Cint being:
Percnt years: Cint(IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

and if not rounded make it a double: Cdbl(Percnt years: IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

Maurice

I am calculating seniority. I start out by using a datedif command as follows.

Seniority: DateDiff("ww",[Hired Date],#3/31/2007#).

This gives me the number of weeks of seniority. I then divide the result
by 52 to get years. So far everything is fine. Then I do a table lookup
based on the 1st number of years to get a multiplier factor. The table has
values from 0 to 4. So if anyone is under 1 year their multiplier is
different from someone who has been employed for more years. The table
values are 20 for 0-1 yrs; 40 for 1-2 yrs etc.

Percnt years: IIf([years] Is Null,'',(DLookUp("percentage","tblprcnt","yrs
=" & Left([Years],1))))

This works great except for 1 rotten person. This person hired in 3/5/07.
Their seniority comes back with 0.05769.... and the table look up does not
equate to 0. What is wrong?

 

[Guest]

Bunky,

How about rounding it yourself by 0.05 that way you should always get a
minimum of 0.1 (which should do the trick because you stated that 0.1 did
work).

Let me know if this does the trick for you otherwise i'll look into it a bit
more.

btw: I noticed that the Cdbl in my previous answer wasn't in the correct
place it should be after the columheading...

Maurice

Maurice,

I tried both and got an error on the calculation.

I am simply wanting Access to look at a number of 0.05769, match it in a
DLookup to the '0' in the table. If the number is 0.1...., the matchup
occurs but values less than .1 do not get a valid match. I thought it was a
rounding problem but now I do not think so..... Ideas?

Bunky,

I'm not sure if you wanted rounded or not.

If you want it rounded then place your IIf-stantement after a Cint being:
Percnt years: Cint(IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

and if not rounded make it a double: Cdbl(Percnt years: IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

Maurice

I am calculating seniority. I start out by using a datedif command as follows.

Seniority: DateDiff("ww",[Hired Date],#3/31/2007#).

This gives me the number of weeks of seniority. I then divide the result
by 52 to get years. So far everything is fine. Then I do a table lookup
based on the 1st number of years to get a multiplier factor. The table has
values from 0 to 4. So if anyone is under 1 year their multiplier is
different from someone who has been employed for more years. The table
values are 20 for 0-1 yrs; 40 for 1-2 yrs etc.

Percnt years: IIf([years] Is Null,'',(DLookUp("percentage","tblprcnt","yrs
=" & Left([Years],1))))

This works great except for 1 rotten person. This person hired in 3/5/07.
Their seniority comes back with 0.05769.... and the table look up does not
equate to 0. What is wrong?

 

[Guest]

Maurice,

Things are getting kinda weird. I forced the calculation of the number of
weeks to only have 1 decimal and got the .1 Then ran that value through as it
was with no other changes and it did not give me the value for 0. Then for
fun and giggles, I changed the persons hire date to give them more time in
job. The value calculated with no limit on decimals to .10020101 and that
gave me the correct value. So I can conclude if I force the rounding it will
not work right but if I have a value that is calculated to .1....... it does
work correctly.

btw - yes I caught that but thanks.

Bunky,

How about rounding it yourself by 0.05 that way you should always get a
minimum of 0.1 (which should do the trick because you stated that 0.1 did
work).

Let me know if this does the trick for you otherwise i'll look into it a bit
more.

btw: I noticed that the Cdbl in my previous answer wasn't in the correct
place it should be after the columheading...

Maurice

Maurice,

I tried both and got an error on the calculation.

I am simply wanting Access to look at a number of 0.05769, match it in a
DLookup to the '0' in the table. If the number is 0.1...., the matchup
occurs but values less than .1 do not get a valid match. I thought it was a
rounding problem but now I do not think so..... Ideas?

Bunky,

I'm not sure if you wanted rounded or not.

If you want it rounded then place your IIf-stantement after a Cint being:
Percnt years: Cint(IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

and if not rounded make it a double: Cdbl(Percnt years: IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

Maurice


:

I am calculating seniority. I start out by using a datedif command as follows.

Seniority: DateDiff("ww",[Hired Date],#3/31/2007#).

This gives me the number of weeks of seniority. I then divide the result
by 52 to get years. So far everything is fine. Then I do a table lookup
based on the 1st number of years to get a multiplier factor. The table has
values from 0 to 4. So if anyone is under 1 year their multiplier is
different from someone who has been employed for more years. The table
values are 20 for 0-1 yrs; 40 for 1-2 yrs etc.

Percnt years: IIf([years] Is Null,'',(DLookUp("percentage","tblprcnt","yrs
=" & Left([Years],1))))

This works great except for 1 rotten person. This person hired in 3/5/07.
Their seniority comes back with 0.05769.... and the table look up does not
equate to 0. What is wrong?

 

[Guest]

Maurice,

Just wanted to let you know I tried to break the query into 2 with the
second one just doing the DLookup. Still did not work.

Any other thoughts would be welcome.

Kent

Maurice,

Things are getting kinda weird. I forced the calculation of the number of
weeks to only have 1 decimal and got the .1 Then ran that value through as it
was with no other changes and it did not give me the value for 0. Then for
fun and giggles, I changed the persons hire date to give them more time in
job. The value calculated with no limit on decimals to .10020101 and that
gave me the correct value. So I can conclude if I force the rounding it will
not work right but if I have a value that is calculated to .1....... it does
work correctly.

btw - yes I caught that but thanks.

Bunky,

How about rounding it yourself by 0.05 that way you should always get a
minimum of 0.1 (which should do the trick because you stated that 0.1 did
work).

Let me know if this does the trick for you otherwise i'll look into it a bit
more.

btw: I noticed that the Cdbl in my previous answer wasn't in the correct
place it should be after the columheading...

Maurice

Maurice,

I tried both and got an error on the calculation.

I am simply wanting Access to look at a number of 0.05769, match it in a
DLookup to the '0' in the table. If the number is 0.1...., the matchup
occurs but values less than .1 do not get a valid match. I thought it was a
rounding problem but now I do not think so..... Ideas?

:

Bunky,

I'm not sure if you wanted rounded or not.

If you want it rounded then place your IIf-stantement after a Cint being:
Percnt years: Cint(IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

and if not rounded make it a double: Cdbl(Percnt years: IIf([years] Is
Null,'',(DLookUp("percentage","tblprcnt","yrs > =" & Left([Years],1)))))

Maurice


:

I am calculating seniority. I start out by using a datedif command as follows.

Seniority: DateDiff("ww",[Hired Date],#3/31/2007#).

This gives me the number of weeks of seniority. I then divide the result
by 52 to get years. So far everything is fine. Then I do a table lookup
based on the 1st number of years to get a multiplier factor. The table has
values from 0 to 4. So if anyone is under 1 year their multiplier is
different from someone who has been employed for more years. The table
values are 20 for 0-1 yrs; 40 for 1-2 yrs etc.

Percnt years: IIf([years] Is Null,'',(DLookUp("percentage","tblprcnt","yrs
=" & Left([Years],1))))

This works great except for 1 rotten person. This person hired in 3/5/07.
Their seniority comes back with 0.05769.... and the table look up does not
equate to 0. What is wrong?

 

[John W. Vinson]

Things are getting kinda weird. I forced the calculation of the number of
weeks to only have 1 decimal and got the .1 Then ran that value through as it
was with no other changes and it did not give me the value for 0. Then for
fun and giggles, I changed the persons hire date to give them more time in
job. The value calculated with no limit on decimals to .10020101 and that
gave me the correct value. So I can conclude if I force the rounding it will
not work right but if I have a value that is calculated to .1....... it does
work correctly.

Double (and Float) number datatypes ARE APPROXIMATIONS. They're stored as a
binary fraction times an exponent. Just as a fraction like 1/7 cannot be
represented exactly as a decimal number, so many fractions such as 0.1 cannot
be represented exactly as a binary fraction. It's an infinite repeating value,
and Float gives only 24 bits, Double 48. As a result, expressions will not
come out "even".

The solutions that come to mind are:

Use a Decimal datatype rather than Float or Double.
Use a Currency datatype rather than any sort of number.
Don't compare to 0, but use a "fuzz factor":

IIF(Abs(<expression>) < 1.0E-6, <zero, close enough>, <nonzero>)


John W. Vinson [MVP]

 

[Guest]

John,

Amazing.
I had been expermenting with putting an iif prior to the Dlookup and messing
around with the data types. I ended up with fixed and did the following

expr: IIf([years] Between 0 And 0.099,0,[years])

and the Dlookup worked. I settle on this about 2 minutes prior to your
post.
To All
Thanks for the help; it is certainly appreciated.