Thanks I did manage to sort that actually and it does work now so thank you. I do have another small problem tho. Its not giving me the correct value in the cohort field. For example in the table as follows (shortened version)

Start Date End Date Cohort
01/01/1900 31/07/03 Pre
01/08/03 31/01/04 1
01/02/04 31/07/04 2

If I add 02/08/03 it answers Pre, 03/10/03 also answers...

Thanks for your help. I have created the table and put in the code as you have suggested but when i enter the date into the form i get this message

Compile error: Method data member not found

and the txtCohort bit in the code is highlighted. Could it be a problem that the the field in the newly created tbl is called Cohort and the field in the form is also called Cohort? how do I proceed?

This may be abit basic, I am a self taught Access user. I have a form in my database to add soldiers details and they are assigned a cohort number depending on when they enlisted. For example if they enlisted between 31/07/03 and 01/02/04 then they are in cohort 1, if they enlisted between 01/02/04 and 01/08/04 then they are cohort 2 and so on.

I want the Cohort number field to automatically upate when I enter their enlistment date...