Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: Help on calculating a date to run a report on

    I am trying to write an expression in a query that will calculate a date. I want to take a birth date and come up with January 1 of the previous year. For example, if I have a birth date of 10/25/86, I want the calculated date to show 1/1/85. Or if I have a date of 5/26/83, I want it to be 1/1/82. Any help in this matter would be greatly appreciated.


    Thanks!

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    Parse off the year from the birthdate:

    myYear=Year(Birthdate)

    NewYear=myYear-1

    Debug.print NewYear

    Then add the day and month to derive your new date.

    HTH

  3. #3
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    Thanks for the reply. I can get the year, but how do I add the month and day to it. If I take that field and do this in my query:

    New Date: "1/1/" & [Year]

    I get a mismatched message because it is text I guess. What is the proper way to do this?

    Thanks,

    David

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select dateserial(year(somedate)-1,1,1) as prevjan1
    from yourtable

    rudy
    http://r937.com/

  5. #5
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    That worked like a charm.

    Thanks, I really appreciate it.

    David

Posting Permissions

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