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 20:27. Reason: added probable fix

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,477
    Provided Answers: 11
    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 00:56. Reason: splelling
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  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
  •