Summing Question 1

[NewComer]

Our database has a SQL backend and a Visual Basic Frontend for data entry. I often create reports via Microsoft Project. My question, in a query I am summing two integer fields, if their are no manhours for one of the fields it is left null. However, when I try to sum them it will not sum unless I add zeros in the null field. I really wanted to leave them blank if possible because it it easier to see if something is inadverately forgotten (admittedly not the best reason). Is there some syntax I can use for the null field when calculating or should I just make it default to zero?

Thanks for you help.

 

[BillPower]

Try these:

SELECT IIf(IsNull([ManHours1]) And IsNull([ManHours2]),0,IIf(IsNull([ManHours1]) And Not IsNull([ManHours2]),0+[ManHours2],IIf(Not IsNull([ManHours1]) And IsNull([ManHours2]),[ManHours1]+0,[ManHours1]+[ManHours2]))) AS TotalManhours
FROM myTable;

SELECT Sum(IIf(IsNull([ManHours1]) And IsNull([ManHours2]),0,IIf(IsNull([ManHours1]) And Not IsNull([ManHours2]),0+[ManHours2],IIf(Not IsNull([ManHours1]) And IsNull([ManHours2]),[ManHours1]+0,[ManHours1]+[ManHours2])))) AS TotalManhours
FROM myTable;

Replace MyTable with you Table name.
Replace ManHours1 and ManHours2 with your Field names

 

[NewComer]

Thank you Bill, will try it first thing Monday morning when i get to work and let you know how it goes...thanks again.

 

[NewComer]

Just another quick question, would the Nz function work in this situation? Thanks

 

[BillPower]

Hi NewComer,
You've solved it yourself, I'm definitely slipping!!!

SELECT NZ([ManHours1])+NZ([ManHours2]) AS TotalManhours
FROM myTable;

 

[NewComer]

Thanks so much for taking the time to help me. I really love this site !!!