Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    151
    Provided Answers: 1

    Unanswered: Dates Between -- The July 31st Problem.

    Is it me, or am I losing my mind:

    I kept missing two July 31st records with the following query:

    >= #7/01/2017# and <= #7/31/2017#

    OR

    between #7/01/2017# and #7/31/2017#

    BUT

    I got all the records by comparing the datevalue of a table date (i.e. datevalue(([adate]) with specified date values such as :

    between datevalue( #7/01/2017#) and datevalue(#7/31/2017#)

    OR

    >= datevalue(#7/01/2017#) and <= datevalue(#7/31/2017#)

    The underlying SQL looks like this:

    SELECT qryUserUsage.Menage, [depuis] FROM qryUserUsage
    WHERE (((DateValue([depuis])) Between DateValue(#7/1/2017#) And DateValue(#7/31/2017#)));

    I think I intuitively understand: dates are in fact numbers, and even short dates entered into tables probably encode the dates and times together. Thus a date entered earler in the day would in fact have a different number from a date entered later in the day (or even a specified date such as #7/31/2017#, depending on how a date number is assigned (or when it is entered??), Datevale, I gather, is providing a value based on the string value of the short date (and time is ignored).

    Thoughts? Makes one feel stupid, especially after working (as a volunteer) on a food bank's data bases for two year.

    J Smith
    Aylmer, Quebec

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Question Is the date feild in the database a date feild?
    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

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    151
    Provided Answers: 1
    You are probably right: it is a date field compiled into a cross tab (part of "group by"). This, I think, turns it into a string. Could be the basic source of all the trouble. So probably ignore my former rant.

  4. #4
    Join Date
    Aug 2017
    Posts
    13
    Provided Answers: 1
    or it's a datetime data type with a timestamp included?

    between datevalue( #7/01/2017#) and datevalue(#7/31/2017#) will catch anything up to midnight on the 31st but if it happened at 1 am, for example, it's past your end date of the between. If it's a datetime and the time is specified you may have to include up through 23:59:59 on the ending date

Posting Permissions

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