Calculations in a query

[bishop0071]

Hello,

I have field [Cardio], which hold numbers. In my database I only allow 16 points for that field. The week are from Saturday-Friday.


1). Right now I created another field, [16PointCardioMax], and just put in how many ponts they have left for the week. Example, if [Cardio] for week adds up to 8, then in [16PointCardioMax], I would type in 8 points left. Is there a formula that I can use to automate this for me? I tried Cardio Max: 16-[16PointCardioMax] as an expression but that does not work.

2.) Is there a way to clear that field, [16PointCardioMax], once the week is up?

3.) I just want that equation to work for the current week. So this current week is from Saturday, Jan. 26th - Friday, Feb. 1st.

[June7]

1. Why doesn't the expression work - what happens?

2. & 3. An expression in query cannot do this - would require VBA code behind some event.