The formula below did not show a zero in the resultant field
Sum(IIf(IsNull(query1dates.fdate),0,[fhours])) AS Sumfhours

I had to use Int and Nz to achieve this
Sum(Int(nz(IIf(IsNull(query1dates.fdate),0,[fhours]),0))) AS Sumfhours

Can somebody please explain why.

Brian

What is the data type of fdate? It could be due to the fact that Date/Time is represented as a floating number, and thus produce rounding errors if we try to do something with it outside of Date functions?

Date/Time. Access stores Dates as 8-byte floating point numbers, where the integer part refers to the day, and the fraction part refers to the time. While Now() + 1/24 is theoretically one hour from now, use the DateAdd() function to avoid rounding errors if you plan to use the result in comparisons. When constructing queries, keep in mind that if a Date/Time field contains a fraction part, it will not match a criteria which is just a date: Now() is not equivalent to Date(), though Int(Now()) is equivalent to Date().

Source (http://allenbrowne.com/xbase-05.html)

Sum(IIf(IsNull(query1dates.fdate),0,[fhours]))

In my query it works

Banana
fdate is a short date field, but I don't see why a fault there would cause the TRUE result of the IIF to return a NULL.

Khawar

I'm glad I'm not going daft as I expected it to work, but having redone my query it still doesn't. I guess I will have to remain puzzled, I hope it works for Graham I see that you have responded to his query.

Brian

Ok found the error :o

I was testing the wrong field should have been tmst.fdate , Banana may be pleased to know that it was his comment on the date that led me to it, I tested for Not ISNull and got all 0 so realised the error was not a quirk but me and a fresh look spotted it. :o

Thanks guys

Brian

Actually, I threw that out because I had trouble reading the syntax first time, and somehow thought we were doing sums on dates.

Glad it worked out for you, Brian. :)