Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    I've encountered this before but wrote a work-around. Just encountered it again and decided to sese if anybody knows why this is happening - or more likely, what am I doing wrong.

    I have a date value; and I'm trying to parse the Year, Month & Day values so I can use them in a string.

    If I use the following code it works fine:

    d =#05/27/09#
    ? month(d)
    5

    However, if I put a format function around it I get the following:
    ? format(month(d),"MM")
    01

    Similarly, the day portion returns 27 but if I format it with "DD" I get 26

    and Format(Year(d),"YYYY") returns 1905

    What am I doing wrong here?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're doing too much work!

    With your example d = #5/27/09#, Month(d) returns 5 (the month number). Format(Month(d), "MM") does*not* format the date d as MM but the number 5.

    To understand why this returns "01", you must know that Access stores dates as the number of days since 12/30/1899. So 1/1/1900 is stored as 2, and 5/27/2009 is stored as 39960.

    The number 5 is the equivalent of 1/4/1900, and formatted as "MM" this becomes "01" since the month of 1/4/1900 is January.

    You can simply omit the Year, Month or Day function and apply Format directly to the date d itself:

    Format(d, "YYYY") returns "2009"
    Format(d, "MM") returns "05"
    Format(d, "DD") returns "27"

  3. #3
    Star Lounger
    Join Date
    Jun 2004
    Location
    Lambertville, New Jersey, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Guess I was over thinking that one!

    Once again - You're The Man!

    Thanks
    Lance

Posting Permissions

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