Results 1 to 5 of 5
  1. #1
    stryker1080 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    2

    VBA Glitch using Dates

    Hello,



    I have found a very weird glitch and i cannot figure it out for the life of me. I have a table with a 'Drill_Date' field, which is Date type and formated using short date. I have an entry there for the 5th of October, and it shows up as 5/10/2009 in the table. Good.

    When i use the query builder and search for the record with Drill_Date = #5/10/2009#, it finds the record. Good.

    In VBA , when i code it so that the record gets displayed based on a Calendar object, the record is not found.

    The one thing i noticed is this: If my query searches for #10/05/2009#, then it finds the record. If i search for #5/10/2009# it does not. It seems that the VBA queryies that run change how the date is compared... It seems to take the smallest number out in front for it to find the record.

    Does anyone have any idea how to get around this problem??

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Have a look at your regional settings, Access has this bad habit of Americanising dates.

    David

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should find this link helpful.

  4. #4
    stryker1080 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    2
    I read the link you posted, but this problem i am having is not one of those. The main problem is that when running a ADO recordset.open with a query which searches for the date, the format switches between #mm/dd/yyyy# and #dd/mm/yyyy# depending on what day it is.

    For example: For Oct 5, if i search for the record i have to search this:

    Format 1:#10/5/2009# or mm/dd/yyyy

    If i search for Oct 11, i have to search for this:

    Format 2: #11/10/2009# or dd/mm/yyyy

    If the day number is larger than the month number, then it uses Format 2. If the day number is less than the month number, it uses Format 1.

    It makes no sense, i had to write a function to switch my query around depending on the date for my form to work properly.... I would much rather it to work properly.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It only switches when the Day and Month are ambiguous (values less than 13). Allen Browne's site explains how to deal with this. What part of his solution are you having a problem with?

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

Similar Threads

  1. Due dates
    By Mehbastan in forum Queries
    Replies: 5
    Last Post: 08-14-2009, 08:37 AM
  2. Calculating Dates
    By AJ0424 in forum Programming
    Replies: 1
    Last Post: 07-14-2009, 08:29 AM
  3. Searching Dates
    By knightjp in forum Database Design
    Replies: 2
    Last Post: 08-16-2008, 11:39 PM
  4. Help with Searching Dates
    By rededdie in forum Access
    Replies: 1
    Last Post: 11-02-2007, 08:34 AM
  5. Page numbering glitch
    By kfinpgh in forum Reports
    Replies: 3
    Last Post: 08-17-2006, 08:23 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