Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    4

    Unanswered: Query using Between [Start Date] and [End Date] Throwing back incorrect data

    Hello,

    I have a query that is filtering dates by a range that is determined in a form. the txt boxes in the form are txtStart and txtEnd. In the query, under the date field, I am using the following criteria:

    Between [Forms]![frmDateQuery]![txtStart] and [Forms!][frmDateQuery]![txtEnd]

    When I launch the query, I am getting correct data, accept for one entry, which keeps coming back. For instance, If I enter in the date range as between 11/1/2012 and 11/2/2012, I get all of the 11/1s and 11/2s, but I also get a record from 11/10/2012. It might be worth mentioning that the query does properly filter out all of the other data (11/3/2012, 11/4/2012 etc...)

    Could the problem be in how access determines the values of entered information? Any ideas?

  2. #2
    Join Date
    Nov 2012
    Posts
    4
    Just an update: I tried entering another record on 11/12/2012, and that one also comes up when I run the query filtering to the date range 11/1/2012 - 11/2/2012. So it seems that any date that starts with a 1 is getting registered as between 11/1/2012 and 11/2/2012. I am so confused.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Are you sure that the date field is identified as a date data type in the field's properties? If not, you can get "unpredictable results," which is IBM jargon meaning "garbage."

    In any event, it's always less confusing when using a complete date pattern for date entries, which would be "mm/dd/yyyy". Note the "dd" which requires 2 day numbers. 11/1 would be entered as 11/01. This pattern is a universal pattern which cannot be mistaken. Using 11/1, with only one day number, can be confused by Access. Try it.

    Sam
    Last edited by Sam Landy; 11-27-12 at 21:27. Reason: added probable fix

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I don't like the Between thing i always use the

    >=Format([Forms]![frmDateQuery]![txtStart] ,"mm/dd/yyyy") And <=Format([Forms!][frmDateQuery]![txtEnd],"mm/dd/yyyy")

    even tho the [Forms]![frmDateQuery]![txtStart] is in a dd/mm/yyyy format
    Last edited by myle; 11-28-12 at 01:56. Reason: splelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Nov 2012
    Posts
    4
    Quote Originally Posted by Sam Landy View Post
    Are you sure that the date field is identified as a date data type in the field's properties? If not, you can get "unpredictable results," which is IBM jargon meaning "garbage."

    In any event, it's always less confusing when using a complete date pattern for date entries, which would be "mm/dd/yyyy". Note the "dd" which requires 2 day numbers. 11/1 would be entered as 11/01. This pattern is a universal pattern which cannot be mistaken. Using 11/1, with only one day number, can be confused by Access. Try it.

    Sam
    Thank you for your reply.

    Unfortunately, when I attempt to filter by 11/01/2012 and 11/02/2012, It returns no data. I think this might be because of the way that access automatically turns the short date of 11/01/2012 to 11/1/2012 in the table itself. Should I be using a different date format? Thanks in advance.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    That's strange. Even if the date in the field is a short date, it should recognize the two-digit day info in the query.

    However, please answer my first question.
    Are you sure that the date field is identified as a date data type in the field's properties?
    Also, please copy and paste the query's complete SQL so we can have a better handle on it.

    Sam

Tags for this Thread

Posting Permissions

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