Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    When is a date not a date? (MSAccess2k2/Win2k2Pro)

    The quick answer is, of course - when she brings her mother.... Heh.. a little humor...very little.

    I'm having a problem in a query I'm working on. I'm having to generate some dates based on other dates and then choose which of those generated dates is closest to, but not less than, today's date. Sounds simple enough, right? - well, when I get to the comparison - it seems to be comparing the Month and Day, but not the year. And then it results in the wrong date being chosen. Unfortunate.

    Now, I believe my problem lies in the way Access is regarding my generated dates... I'm taking the Month and Day from a given date (Var) and then using this year for the year and creating a string... like so - Format (Month(Var) & "/" & Day(Var) & "/" & Year(Now()),"mm/dd/yyyy" ). Now, I can DateAdd to this figure successfully, but when I try to choose in an IIf() function - It makes the choice based on the mm/dd and doesn't consider the yyyy...

    How do I get the created string to truly be regarded by Access as a date? When is a date not a date?

    - ooh - though of another one: When she asks you to watch her purse while she disappears into the mosh pit? -- ok, that one was a little extreme... thanks for your help!

    -Dik

  2. #2
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When is a date not a date? (MSAccess2k2/Win2k2Pro)

    Perhaps try resetting your computer regional settings mm/dd/yyyy.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: When is a date not a date? (MSAccess2k2/Win2k2Pro)

    Umm, I won't comment on your jokes, if that's OK with you.

    The Format function returns a string, not a date. Some VBA operations are forgiving enough to interpret this string as a date, but you can't rely on that. It's better to use the DateSerial function. DateSerial(y, m, d) returns the Date value of the date with day = d, month = m and year = y. So, if you have a date value in a variable Var, you can use

    DateSerial(Year(Now()), Month(Var), Day(Var))

    to return the date in this year that falls on the same day and month as Var. The year of Var is ignored because you take the year of Now(). I can't judge whether that is what you need, but at least it returns a date value.

  4. #4
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When is a date not a date? (MSAccess2k2/Win2k2Pro)

    That worked great! I guess the DateAdd working threw me. Of course the IIf() function was comparing like a text string. I noticed this more when I tried analysis of the output data in Excel...

    Thanks so much... now about my humor... <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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