Assistance with this issue would be greatly appreciated. I have created a query that contains a calculated field. The query has the following fields:

[Date by Day] this is grouped by date.
[Associate]
[Sum of Total Hours] this is the calculated field

I would like the [Sum of Total Hours] not to exceed 10 hours. However, I cannot figure out how to accomplish this.

Can somebody please help?

How is your calculation derived? What do you want to display if the calculation results in a sum >10 hours?

I have several hours in a day that are summed up to total hours for that one particular day. Hours for that one day are in a query. I have a total hours query that sums the hours. The total hours query should not exceed 10 hours. I really hope this makes sense.

The calculated field should not exceed 10 hours. If it exceeds 10 hours an Error should be displayed in the field. Or a validation text would be helpful. My goal is to not allow the calculated field to exceed 10.

Thank you.

See the attached

I am unable to open the zip file. Can you please explain what I need to do in order to get the calculated control working?

Create a query with an IIf expression. jfgambit's query is:



SELECT tblHours.Date, tblHours.Associate, IIf(Sum([Hours])>10,"There are too many hours",Sum([Hours])) AS Hrs
FROM tblHours
GROUP BY tblHours.Date, tblHours.Associate;

You may need to change the field names to match yours. If the field names are the same as yours, you can just paste this into the SQL view of a new query and then switch to the normal query grid to see what the result is.

Thank you, it works! But what I would really like is...

How can I make a validation rule so that a validation pops up warning that the number of hours have been exceeded?