Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Date Range with Timestamp

Discussion in 'Information Technology' started by Mark, Nov 8, 2005.

  1. Mark

    Mark
    Expand Collapse
    Guest

    Hi.

    I'm using the parametres Between [DateFrom] And [DateTo] for the basis of
    business written within a month. Unfortunately, this query excludes the
    DateTo (ie. DateFrom = 01-11-05 DateTo = 07-11-05 results in data from
    07-11-05 being excluded). Ive tried >= and <= to no avail. The dates are in
    the Timestamp format of 29-Jun-05 8:08:04 AM.

    Can you please point me in the right direction to make this easier to handle.

    Regards,
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Mark,

    Date/Time values are stored in Access as numbers with decimal values, where
    the number is the number of days since Dec. 31, 1899 (if I recall correctly)
    and the integer portion corresponds to the time of day (0.25 = 6:00 AM, 0.5 =
    Noon, 0.75 = 6:00 PM, etc.).

    If your criteria is written like this:

    Between [DateFrom] And [DateTo]

    You will only pick up records for the DateTo value where the integer portion
    is equal to zero (ie. midnight). Try the following instead:

    >= [DateFrom] AND < [DateTo] + 1


    A DateTo = 07-11-05 would result in all records less than midnight on
    07-12-05 being included in the recordset.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Mark" wrote:

    Hi.

    I'm using the parametres Between [DateFrom] And [DateTo] for the basis of
    business written within a month. Unfortunately, this query excludes the
    DateTo (ie. DateFrom = 01-11-05 DateTo = 07-11-05 results in data from
    07-11-05 being excluded). Ive tried >= and <= to no avail. The dates are in
    the Timestamp format of 29-Jun-05 8:08:04 AM.

    Can you please point me in the right direction to make this easier to handle.

    Regards,
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    An alternative is to strip off the time fraction of your datetime field like --
    CVDate(Int([YourDateField]))


    "Tom Wickerath" wrote:

    > Hi Mark,
    >
    > Date/Time values are stored in Access as numbers with decimal values, where
    > the number is the number of days since Dec. 31, 1899 (if I recall correctly)
    > and the integer portion corresponds to the time of day (0.25 = 6:00 AM, 0.5 =
    > Noon, 0.75 = 6:00 PM, etc.).
    >
    > If your criteria is written like this:
    >
    > Between [DateFrom] And [DateTo]
    >
    > You will only pick up records for the DateTo value where the integer portion
    > is equal to zero (ie. midnight). Try the following instead:
    >
    > >= [DateFrom] AND < [DateTo] + 1

    >
    > A DateTo = 07-11-05 would result in all records less than midnight on
    > 07-12-05 being included in the recordset.
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "Mark" wrote:
    >
    > Hi.
    >
    > I'm using the parametres Between [DateFrom] And [DateTo] for the basis of
    > business written within a month. Unfortunately, this query excludes the
    > DateTo (ie. DateFrom = 01-11-05 DateTo = 07-11-05 results in data from
    > 07-11-05 being excluded). Ive tried >= and <= to no avail. The dates are in
    > the Timestamp format of 29-Jun-05 8:08:04 AM.
    >
    > Can you please point me in the right direction to make this easier to handle.
    >
    > Regards,
    >
     
  5. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Tom Wickerath wrote:
    > Date/Time values are stored in Access as numbers with decimal values, where
    > the number is the number of days since Dec. 31, 1899 (if I recall correctly)
    > and the integer portion corresponds to the time of day (0.25 = 6:00 AM, 0.5 =
    > Noon, 0.75 = 6:00 PM, etc.).


    I think you are a little confused. Under the covers, DATETIME values
    are stored as Double (FLOAT). The values to the left of the decimal
    point correspond to the number of days and the value to the right
    represents the time.

    > >= [DateFrom] AND < [DateTo] + 1


    Here another suggestion (untested)

    BETWEEN INT(CDATE([DateFrom]))
    AND INT(CDATE([DateTo])) + TimeSerial(23, 59, 59)
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Peregenem,

    > I think you are a little confused.

    What do you base your statement on? I stated:

    "Date/Time values are stored in Access as numbers with decimal values,..."
    without identifying the type of number as you did "Double (Float)".

    The only points I'll give you is that I said "where the number is the number
    of days since Dec. 31, 1899", when I should have wrote the number to the left
    of the decimal is the number of days...

    and I included:
    "the integer portion corresponds to the time of day (0.25 = 6:00 AM, 0.5 =
    Noon, 0.75 = 6:00 PM, etc.)."

    I obviously should have said "the decimal portion corresponds .....".
    However, my example clearly indicated that I was using the decimal portions
    of a number to represent the time of day. While I may have made some minor
    inconsequential mistakes due to the time of day (after midnight when I
    posted), I believe any reasonably intelligent person could figure out exactly
    what I was stating. I don't think this rises to the level of "I think you are
    a little confused". A little tired and sloppy, perhaps, but not confused.

    _________________________________________

    "peregenem@jetemail.net" wrote:

    > Tom Wickerath wrote:
    > Date/Time values are stored in Access as numbers with decimal values, where
    > the number is the number of days since Dec. 31, 1899 (if I recall correctly)
    > and the integer portion corresponds to the time of day (0.25 = 6:00 AM, 0.5 =
    > Noon, 0.75 = 6:00 PM, etc.).


    I think you are a little confused. Under the covers, DATETIME values
    are stored as Double (FLOAT). The values to the left of the decimal
    point correspond to the number of days and the value to the right
    represents the time.

    > >= [DateFrom] AND < [DateTo] + 1


    Here another suggestion (untested)

    BETWEEN INT(CDATE([DateFrom]))
    AND INT(CDATE([DateTo])) + TimeSerial(23, 59, 59)
     
  7. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Tom Wickerath wrote:
    > While I may have made some minor
    > inconsequential mistakes due to the time of day (after midnight when I
    > posted), I believe any reasonably intelligent person could figure out exactly
    > what I was stating. I don't think this rises to the level of "I think you are
    > a little confused". A little tired and sloppy, perhaps, but not confused.


    A fellow pedant, eh? :) Can you tell me why you think "values are
    stored in Access"?
     
  8. Mark

    Mark
    Expand Collapse
    Guest

    This sounds logical to me Tom - thanks for the reply.

    I've tried your suggestion and it failed with a "too complex to be
    evaluated...try simplifying the expression..."

    Any ideas?

    "Tom Wickerath" wrote:

    > Hi Mark,
    >
    > Date/Time values are stored in Access as numbers with decimal values, where
    > the number is the number of days since Dec. 31, 1899 (if I recall correctly)
    > and the integer portion corresponds to the time of day (0.25 = 6:00 AM, 0.5 =
    > Noon, 0.75 = 6:00 PM, etc.).
    >
    > If your criteria is written like this:
    >
    > Between [DateFrom] And [DateTo]
    >
    > You will only pick up records for the DateTo value where the integer portion
    > is equal to zero (ie. midnight). Try the following instead:
    >
    > >= [DateFrom] AND < [DateTo] + 1

    >
    > A DateTo = 07-11-05 would result in all records less than midnight on
    > 07-12-05 being included in the recordset.
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "Mark" wrote:
    >
    > Hi.
    >
    > I'm using the parametres Between [DateFrom] And [DateTo] for the basis of
    > business written within a month. Unfortunately, this query excludes the
    > DateTo (ie. DateFrom = 01-11-05 DateTo = 07-11-05 results in data from
    > 07-11-05 being excluded). Ive tried >= and <= to no avail. The dates are in
    > the Timestamp format of 29-Jun-05 8:08:04 AM.
    >
    > Can you please point me in the right direction to make this easier to handle.
    >
    > Regards,
    >
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Ok JET.....but most people use the term Access and JET interchangeably (even
    though they're distinctly different). Similarly, most people use the terms
    "weight" and "mass" interchangeably, even though these two measures are very
    different. Do I get any heartburn over this? Of course not.

    Would you like for me to start scrutinizing every post you make? I can do
    that if that's the game you want to play.

    Tom
    ____________________________________________

    "peregenem@jetemail.net" wrote:

    A fellow pedant, eh? :) Can you tell me why you think "values are stored in
    Access"?
     
  10. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Mark,

    Sorry, I didn't notice your second post due to the unnecessary noise
    generated by "peregenem". Try this form, which makes use of the built-in
    DateAdd function to add 1 day:

    >=[DateFrom] And <DateAdd("d",1,[DateTo])


    You can look up DateAdd in Visual Basic Help if you want to learn more about
    this function. The form I gave you the first time is actually more in line
    with writing the WHERE portion of a SQL statement in code. For example, the
    following line of code comes from a working function:

    IncludeDates = IncludeDates & " AND ([AnswerDate] < #" & Me!txtEndDate + 1 &
    "#)"

    Sorry about the confusion.

    Tom
    _______________________________________________

    "Mark" wrote:

    This sounds logical to me Tom - thanks for the reply.

    I've tried your suggestion and it failed with a "too complex to be
    evaluated...try simplifying the expression..."

    Any ideas?
     

Share This Page