Maximum number in Number field

[Bhoc]

Hi again
Still working on a golf tournament database. Have searched and racked what little brain I have left over this and can't work it out.
With a new handicap system coming in force handicaps for players have to be calculated by multiplying 2 figures then dividing by another one.
I have used a calculated field in a table to work this out and it works great. The problem is that the result is not to exceed 45. This is where my problem lies. How do I ensure that this field (named handicap in table players) does not exceed the number 45.
I am able to change the field in the table from a calculated field and allow this to be populated by the form (players) which can do the calculation in non visible fields and then show this to the user in another text box which has handicap as its control source.

Help please.
:banghead:

 

[Cronk]

You can set rules in the design view of the table. Click on your field and in the Field properties at the bottom, type for Validation Rule <46

You can customize the message when the value is more than 45 by putting your message in the Validation Text.

 

[Bhoc]

Thanks Cronk but I am aware of validation and it is not this I am looking for. The field that has the handicap is a calculation whereby after entering the players own club handicap this is multiplied by the playing course's slope rating and divided by 113. (Don't ask how they come up with that formula - it is golf after all !!!!)
What happens when this formula is run with some players having large handicaps the formula takes them over 45 - the handicap has to be capped at 45 so therefore I am looking for some way to have any numbers over 45 reduced to 45 automatically. Hope that makes more sense.
For an example; Player 1 has a base handicap of 44 when the formula is applied to his handicap for the course they are playing on the handicap becomes 48 when the slope rating for that course is around 125 - I need this players handicap to be reverted back to 45 automatically without the user having to revert to a calculator

 

[Cronk]

Sort answer (long one later if needed).

In the AfterUpdate event of the entered fields, call a function to evaluate the result. If the result is more than 45, reduce the result to 45.

 

[Bhoc]

Cronk thanks for that - may need to know what function - I think problem may be also that the field is a calculated field which of course is locked - I am using 2010
May have to somehow change the field from calculated - your suggfestions??

 

[Cronk]

My previous post was meant to be "Short answer..."

The function is one you write. You said there are three numbers in the calculation so the procedure would be

Sub CalcHandicap()
  dim lngHandicap
  lngHandicap= me.txtNumber1 * Me.txtNumber2/me.txtNumber3
  if lngHanicap >45 the lngHandicap=45
  Me.txthandicap= lngHandicap
end Sub

You would need to test the existence of entries in the text boxes and that txtNumber3 was not zero

 

[Bhoc]

Cronk

thanks for this - it worked - had big problems as I had used a calculated field in the table but have fixed that now and everything is great