Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Default Value Formula

    Trying to write a formula for a default value for a "Year" control on a form.

    DistrictRecharterMonth is a numeric (ex. 6 = Jun); and CouncilName is alpha; both in the table tCouncilDefaults. I want the default to show the previous year up to one month before the recharter month.
    For example if the recharter month is 6, then the default would be 2013 up until May 2014.

    I've tried many variations of the following with/without "", [], etc.
    I suspect I'm using the wrong syntax somewhere.

    =Year(DateAdd([m],-(DLookUp("[DistrictRecharterMonth]","[tCouncilDefaults]","left([CouncilName],10)='Great Lake'"))-1,Now()))
    PGT

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Check that the expression:
    Code:
    DLookUp("[DistrictRecharterMonth]","[tCouncilDefaults]","left([CouncilName],10)='Great Lake'")
    returns the proper value.
    2. Try:
    Code:
    =Year(DateAdd("m",-(DLookUp...
    3. If you use the expression in the property window, try changing , to ;.
    Code:
    [DistrictRecharterMonth]";"[tCouncilDefaults]";"left...
    Have a nice day!

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Thanks Sinndho,
    Many more experiments in the immediate window yielded the following that worked:
    year(dateadd("m",-DLookUp("DistrictRecharterMonth", "qCouncilDefaults", ("[CouncilName]like 'Great Lake*'"))-1,now()))
    Pete
    PGT

Tags for this Thread

Posting Permissions

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