"I want to validate the 2nd field (weight) so that it checks the numeric range entered for the second field(Weight)"
Are you sure? I think you may have mis-typed. Please give us more (correct) info and everyone will be happy to help.
This isn't very elegant, but I think it will work for your needs.
In the field Validated by Calculation put this formula, add your other parameters for gestation.
Gestation = 24 and Weight < 200 ; 0 ;
Gestation = 24 and Weight > 1200 ; 0 ;
Gestation = 25 and Weight < 300 ; 0;
Gestation = 25 and Weight > 1400 ; 0;
Out of range numbers get some nasty error messages which will make little sense to the average user about reverting the record so you might want to put the limits somewhere on the layout to give your users a clue as to what is the range for each gestation value.
Thanks very much to Bumper and Rick Whitelaw for your feedback.
I will try the above calculation you have mentioned.
The Case calculation will be very lengthy if you need to write it to encompass around 20 gestations, each having a min and max weight.
What might be a better approach, if you have the experience, is to create a "Gestation" table with each Gestation having its own min weight and max weight fields. Then create a many to many relationship with your current neonatal table.
With the creation of the relationships and some additional fields, your validation calculation could simply be reduced to something like this:
If ( neotanal_weight* ≥ weight_min* and neotanal_weight* ≤ weight_max* ; 1 ; 0 )
*these are made-up field names.
Many thanks Mr RDowler
Don't have that experience (being a medic!) but used your suggestion with additional tables and created a relationship and it works fine