Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: default field based on other fields

    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.


    Details are:

    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.

    For example:
    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???

  2. #2
    Join Date
    Aug 2008
    Posts
    3
    Sorry, mistyped that Iif. I should have put:

    iif(month([agree_date])<=6, year([agree_date]), year([agree_date])+1)

    I just missed a bracket.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2008
    Posts
    3
    StarTrekker ... geez you are fast!!

    Thanks for that input.
    I kind of thought there would have to be a variable temporary value held to do it, but I wondered if there was any other way.

    Oh well, if I need to do it in the forms, then so be it.

    Cheers

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •