Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    Unanswered: how many months since last March?

    hi, ok this is either going to be impossible or incredibly easy....I need a textbox on a report to be (the value of another box on the report multiplied by) a value which equals the number of months since the end of the last March, well kind of anyway...in April this value should be 1, then on the first day of May the value should become 2, right through to 11 at the beginning of Feb, then 12 at the beginning of March, and back to 1 again on April 1st...
    I'm stumped! Any suggestions?
    Thanks in advance!

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    Bane,

    I don't use Access so don't know the syntax. Something like this seems OK =12-MOD(3-MONTH(Today),12)

    regards,
    Fazza

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at datediff
    have a look at the year(), month() and day() fucntions
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Unfortunatly the MOD() worksheet function does not exist in Access, and the Mod operator does not do the same thing (!?) but looking at the definition of MOD() in Excel leads me to this:-

    Month from previous March
    = (Month(Date) - 3) - 12 * Int((Month(Date) - 3) / 12)

    The integer divide returns -1 when 'Date' is before March and zero when March or later

    Is this 'better' than using DateDiff() etc ??

    MTB

  5. #5
    Join Date
    Feb 2006
    Posts
    56
    use the datedif() and use the format command with datedif to get the result you are looking for.

  6. #6
    Join Date
    Oct 2004
    Posts
    75
    the datediff function worked beautifully, thanks all!

  7. #7
    Join Date
    Mar 2006
    Posts
    33
    Provided Answers: 1
    if you leaveout the year when using the datediff function access always substitutes the current year

Posting Permissions

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