Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    138

    Unanswered: Expression to Round Dates to the First of the month

    I need to round to the first day of the month

    I have a query for employees 3 month evals due, their hire dates vary through out the month currently my expression is [Employees]![HireDate]+90 but if they were hired 01/20/2004 I need the due date to be 04/01/2004 not 04/20/2004. Any suggestions

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    =str(Month([field])) + "/1/" + Str(YEAR([field]))

  3. #3
    Join Date
    Dec 2003
    Posts
    138

    Rounding date

    Originally posted by Teddy
    =str(Month([field])) + "/1/" + Str(YEAR([field]))
    I need the date to be 3 months after the hire date how can I get it to calculate the due date by adding 3 months to the hire date, this expression is saying it is due the same month they were hired

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Rounding date

    Originally posted by dendalee
    I need the date to be 3 months after the hire date how can I get it to calculate the due date by adding 3 months to the hire date, this expression is saying it is due the same month they were hired
    Apply logic.

    =str(Month(DateAdd("m", 3, [field]))) + "/1/" + Str(YEAR(DateAdd("m", 3, [field])))

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Just add 90 days to the value of [Field]

    Teddy basically had it correct

    =str(Month(dateadd("m",3,[field]))) + "/1/" + Str(YEAR(dateadd("m",3,[field])))

Posting Permissions

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