Results 1 to 7 of 7
  1. #1
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25

    Question SQL Month function behaving oddly

    When I call the MONTH function on a date where the day is higher than 12, it works properly. But when I do it with a date where the day is less than or equal to 12, it returns the day value. So

    MONTH(#23/03/2000#) returns 3 but
    MONTH(#03/09/2000#) returns 3 as well

    I double-checked the format of my dates as they are stored in the table, as well as how they are entered in the text-box before I click search on my form. It is the DD/MM/YYYY format.

    The Query is "SELECT * FROM Employees WHERE MONTH(birthday) = MONTH(#21/05/2000#)"

    I set this query as the record source of my form and it's being populated with the wrong results.

  2. #2
    RagJose is offline Seasoned user
    Windows XP Access 2002
    Join Date
    Apr 2012
    Location
    Brazil
    Posts
    42
    Could not replicate your problem. What is your locale? Do your Office and your Windows "speak" the same language? Maybe there is a conflict between Windows and Access settings in that area, causing MONTH to fall back to MM/DD/YYYY when it sees a chance. Does not make much sense, but who knows?

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    A useful link for such an issue: http://allenbrowne.com/ser-36.html

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What is your location? I've been looking, but I can't find the documentation in Access the talks about dates and date formats.

    What I remember is: regardless of the regional settings of your PC, you must provide dates in US format (ie MM/DD/YYYY) for SQL statements in VBA. The month function is smart enough to know there are no month values greater that 12, so if you pass a date like #22/3/2011#, it correctly returns 3. But if you pass a date like #4/5/2011#, the function assumes that the format is in US format and returns 4 as the month, not 5.


    Edit: That is what I was looking for, Allan...

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by ssanfu View Post
    Edit: That is what I was looking for, Allan...
    Just doing my duty here.

  6. #6
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25
    I used a combination of VB's and SQL's Month functions to finally coax out some consistant behavior

    'VBA Month Function
    FieldValue = Month(FieldValue)

    'SQL Statement
    Query = "SELECT * FROM Employees WHERE MONTH(birthday) = " & FieldValue

    It did the trick anyway. Thanks!

  7. #7
    bonecone is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    25
    If anyone is interested out of curiosity, I'm in Canada and I'm using Access 2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ADODB Recordset behaving strangely
    By RayMilhon in forum Programming
    Replies: 4
    Last Post: 11-04-2011, 12:21 PM
  2. Recordset not behaving as expected
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 02-15-2011, 04:25 PM
  3. intellisense not behaving
    By avianrand in forum Programming
    Replies: 3
    Last Post: 06-18-2010, 04:59 PM
  4. month function..
    By thewabit in forum Programming
    Replies: 13
    Last Post: 04-19-2010, 10:01 PM
  5. Replies: 0
    Last Post: 10-21-2008, 10:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums