Query Results in #Num! - Can't perform calculations 1

[MattGreer]

Using Microsoft Access 2010
I have a database built to total people's billable hours vs overhead hours but also keep track of vacation, which is not used in the calculation of "billability".

The calculation goes like this:

[tt]Billable
---------------
Billable + Overhead[/tt]

So if a person takes a week of vacation, I'm dividing by zero and I can't average the person's billability over time as Access can't use #Num! in a calculation.

How do I avoid this situation?

Here's the query:

[tt]SELECT tblPeople.FirstName, Format([Billable]/([Billable]+[Overhead]),"Percent") AS BillRate
FROM tblPeople INNER JOIN tblHours ON tblPeople.ID = tblHours.PersonID
ORDER BY tblPeople.FirstName;
[/tt]


Thanks!!


Matt

 

[MattGreer]

OK, saw another thread in this forum that referenced the IIF statment, so I googled that and I've solved my problem. If Vacation Time = 40, return a value of 0% to the field in the query.

Thoughts on whether or not that's the "best" solution?

Thanks!!


Matt

 

[MajP]

Probably. Or you could exclude this cases with... where (billable + overhead) > 0

 

[MattGreer]

Ooo, I like your suggestion better. I don't know if it's possible, but it could be that someone takes 40 hours of vacation but then we make 'em come in on Saturday or something.

How would I incorporate that WHERE clause into my query? And then I'd need to make sure to put some value into the query result, wouldn't I? Or would Access just ignore the blank space?

Thanks!!


Matt

 

[MajP]

I am not sure I understand your question

And then I'd need to make sure to put some value into the query result, wouldn't I? Or would Access just ignore the blank space

You would not have any "blank spaces". If billable + overhead = 0, no record would be returned.

SELECT tblPeople.FirstName, Format([Billable]/([Billable]+[Overhead]),"Percent") AS BillRate
FROM tblPeople INNER JOIN tblHours ON tblPeople.ID = tblHours.PersonID WHERE (Billable + Overhead) > 0
ORDER BY tblPeople.FirstName

 

[MattGreer]

Correct. Problem is, if a person wasn't billable for a week I do need that 0% returned. Either way, I got my answer. Thank you!

Thanks!!


Matt