Weird Output from Calculated If Statement

[andy_dyer]

Ok...

I have the following query:

SELECT qryDataWeeklyHours.[User Name], qryDataWeeklyHours.[Timesheet Period], qryDataWeeklyHours.[Weekly Hours], qryDataWeeklyHours.[Discretionary Hours], IIf([discretionary hours]>=0,[discretionary hours],"0") AS [New Discretionary Hours]
FROM qryDataWeeklyHours
GROUP BY qryDataWeeklyHours.[User Name], qryDataWeeklyHours.[Timesheet Period], qryDataWeeklyHours.[Weekly Hours], qryDataWeeklyHours.[Discretionary Hours];


I have a seperate query that feeds this one that totals the amount of hours worked in a week for every member of staff and then calculating the difference between this and the standard 37.5 hours.

This query should take that and then check if the discretionary hours (difference between the two) is + or - if positive then keep the number if - then replace the number with a 0...

It appears to do this for most lines but for some it is doing something weird...

Weekly Hours Discretionary Hours New Discretionary Hours
37.58 0.079999999999998 7.99999999999983E-02
39 1.5 1.5

For the top example it's doign something with the decimal places that i don't understand... sorry if this is really simple...

Anybody help??

 

[raskew]

Hi -

What are the datatypes of your hours fields?

Bob

 

[Dennisk]

your numbers are being displayed in scientific notation.

E-2 means raise the number(Up to but not including the 'E') to -2

try formatting to one decimal place

 

[DCrake]

Andy

Take out the quotes from around the 0 in your iif statement and retry

 

[andy_dyer]

Originally my hours fields are all general numbers and should be limited to 2 decimal places but all this seems to go out of the window when i start calculating them...

 

[andy_dyer]

Andy

Take out the quotes from around the 0 in your iif statement and retry

It worked! I don't know why but it worked!

Thanks for your help!

 

[DCrake]

Because you substituted "0" with 0 meant that it changed from being a text type value into a numeric value so it could perform a calulation on it.

DC