Calculated field OR manually entered value?

[ComradeGrumbles]

I have an interesting question for anyone who'd like to help me tackle this.

I have a calculated field that is a total based on 5 other fields. These fields are rankings of priority on individual categories, while the calculated field is a total priority ranking for the entire record. (A ticket) The 5 categories are prioritized, and the calculated field runs it through our equation to determine the overall priority of each ticket.

Now, some tickets don't have individual priority numbers in their categories. Therefore, no total priority number is calculated in the calculated field. We would like these tickets to be given a manual total priority, but we can't manually enter numbers into the calculated field to do this.

Is there a way of saying this to the calculated field... "Use the equation to run the individual categories to come up with your total. IF there are no numbers in those categories, reference the field 'Manual Priority' to find your number."

This way, we'd like to keep all of the total priorities in the calculated field. It's just that some of them have to be manually entered because there will be no individual priority categories to calculate the total by.

The current formula for this total priority field is...

(([Size of Financial Risk?]*20)+([Impact on Internal Customers]*15)+([Non-Financial Impact]*20)+([Impact on External Customers]*30)+([Estimated # of Clients Impacted]*15))

I'd like to say, "Do that... or use this manual field if that equation turns out to be nothing."

Any ideas? I appreciate any help you can give.

 

[vbaInet]

Your form is a Continuous form or in Datasheet view right?

 

[ComradeGrumbles]

This particular function will really only be used in datasheet view. Should a ticket not require individual priority categories, a user will go into the datasheet and enter a manual total priority into a field.

Preferably, the calculated total field will pick this up. The calculated total field is then displayed on our forms.

 

[vbaInet]

In datasheet view you can't enter data to an unbound textbox for individual records. But if you mean just one total priority textbox somewhere in the header or footer, then you can add that to your Sum()

 

[vbaInet]

=Sum([[COLOR="blue"]Field[/COLOR]]) + [COLOR="Blue"]txtManualSum[/COLOR]

 

[ComradeGrumbles]

I'm a bit of a beginner with Access. But if I understand you correctly, you're saying that this new field would simply be added to the calculation?

As in, the calculated total comes up as 0 when the individual category fields are empty. Therefore, we simply add another number to that in order to manually enter our priority score?

 

[vbaInet]

I've just noticed you made an amendment to the original post after I posted and I think I have a clearer understanding.

What you can do is use the IIF() function. Here's a link:
http://www.techonthenet.com/access/functions/advanced/iif.php

Your code would look like so:

IIF((([Size of Financial Risk?]*20)+([Impact on Internal[COLOR="blue"]...etc[/COLOR])) = 0, [[COLOR="blue"]Manual Field[/COLOR]], (([Size of Financial Risk?]*20)+([Impact on Internal[COLOR="Blue"]...etc[/COLOR])))

 

[ComradeGrumbles]

This worked out very well! Thank you!

All I had to do was add IIF(IsNull(... instead of just having IIF((... and then ...=0,[Manual Field]...

It's working perfectly now, thanks again!

 

[vbaInet]

You're welcome!