I want to set a rule in my table that restricts the value that the user can input, based on the value of another field. for example, if the field 'Pack' is '1 by 1 the only value the user can input into the field 'split' is 0. If the field 'Pack' is 2 by 1, 3 by 1 (etc), then the user can input any number. I presume the left function will be required somewhere, cheers.
I don't think you can build this type of sophistication in a JET database. You can if your back end (datastore) is a server, but not in the default data store in Access (JET).
So you will have to build your validation into a form.
To do this place some code in your controls on change, on lost focus events. You will need to consider how you intend to handle chnages in packsize which would invalidate exisiting data, eg if the packsize was 4 & the split set to 2, what happend if you change the packsize to 2
your validation routine should be stored in a fucntion of its own so it can be called from any other event you think appropriate (eg the forms before update, on current)