#Error in calculation

[brett429]

I have a few simple calculations on my form that work like this:

[ Total Vacation Days ] - [Vacation Days Used ] = [ Vacation Days Remaining ]

Total Vacation Days is a bound field for that particular employee. The others are just forumulas based on queries. I have a set of calculations like this for various types of days (sick days, vacation days, personal days, etc.). If I have at least one record entry for a particular day off type, the calculations work fine. If I don't, the Days Used and Days Remaining show up as #Error. How can I avoid this? I realize WHY it's doing it mathematically, but I'd rather have it show nothing at all or have Days Used default to 0. I don't want to have to go in and put a dummy record for each type of day just to get it to work!

 

[rainman89]

use the nz function

nz([ Total Vacation Days ],0) - nz([Vacation Days Used],0) = [ Vacation Days Remaining ]

 

[RuralGuy]

Ray:
I don't know if Nz() will help when there is no record. May need to use IIF(IsError,,)

 

[brett429]

Where/how do I implement these suggestiosn? I may not be able to due to the strange way I have the formulas calculating. Here are some details, if you can help me out:

I have the first query that finds all the "Vacation" records. Then I have a second query that does a SumOfHours based on the first Vacation query, to get the sum for each employee. Then I have a form based on the second query, which I use as an invisible subform on my main records page.

Then we have the calculations:

Total Vacation Days is a BOUND field called VacationDays
Vacation Used is =frmVacationSumSubform.Form!SumOfHours
Vacation Remaining is =[VacationDays]-[VacationUsed]

I know the way I'm doing this is probably kind of backwards, but I'm not an Access guru, so I do things however I need to.

 

[rainman89]

RG is correct, if there is no record, it can't get a value to look at.

if you are doing this on a form then you will be fine.
you can do like RG said, use an iif statement to check if its blank, if it is, assign 0 and then subtract the time..

 

[brett429]

RG is correct, if there is no record, it can't get a value to look at.

if you are doing this on a form then you will be fine.
you can do like RG said, use an iif statement to check if its blank, if it is, assign 0 and then subtract the time..

Thanks, but can you give me the coding? You're talking to a novice lol.

 

[RuralGuy]

Can you post enough of a sample of your db so we can play with it a bit?

 

[pbaldy]

This help?

http://www.mvps.org/access/forms/frm0022.htm

 

[brett429]

This help?

http://www.mvps.org/access/forms/frm0022.htm

That, my friend, is pure genius. Thank you!

And thanks to everyone who responded to help, I appreciate it!

 

[pbaldy]

LOL! If it's genius to have had the same problem, beat my head to a bloody pulp trying to solve it, and then remember the answer I finally found, I'm a genius!

 

[brett429]

You were a step ahead of me regardless! haha

 

[datAdrenaline]

PMFJI ...

As an FYI ... I beleive I have used something like this with succuss ...

Instead of:
Vacation Used is =frmVacationSumSubform.Form!SumOfHours

Used something like:
Vacation Used is =IIf(frmVacationSumSubform.Form.CurrentRecord = 0, 0, frmVacationSumSubform.Form!SumOfHours)