Well hello there ... first time poster here.
I have an issue whereby I need to default a field based on the data entered in another field.
I know I am probably breaking normalisation rules here (as it is effectively a calculated field) but for ease of use and lookups I need it.
At this stage I would prefer not to put the condition within a form - as there are many forms involved.
field entered by user: agree_date (the date of agreement)
field to be defaulted: financial year
A financial year in Oz is July 1 through to June 30.
(in this case, where an agreement is entered into after July 1, then is is part of the next financial years agreements. Eg, an agreement on October 10 2008 is part of the 2009 agreements)
As I want this to be done at time of record entry of agreement, I have tried using a default value Iif but I keep getting an error message about fields not being recognised.
field entered by user: agree_date
field to be defaulted: financial_year: default value: iif(month([agree_date]<=6, year([agree_date]), year([agree_date])+1)
However, when entering this iif in the default value of the field in the table and trying to save table I get "the database engine does not recognize either the field 'agree_date' in validation expression or the default value in the table.."
I am using MS Access 2003.
So basically my question is ... how can I default the value in a field based on conditional entry of another field within the same table without using validation within a form???
I've never tried too hard to do it since doing data entry at the table level is not wise.
The situation is that you can't do it directly since Access simply doesn't allow references to other fields in table level Default Values. You also can't do it indirectly since the data doesn't exist until you complete the record and save it.
So, in short, you can't. Just do it at the form level.