Date Range with Timestamp

#1 (**permalink**) 08-Nov-2005, 12:52 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6834-date-range-with-timestamp.html
the Timestamp format of 29-Jun-05 8

04 AM.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834

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

Regards,

#2 (**permalink**) 08-Nov-2005, 12:52 PM

Hi Mark,

Date/Time values are stored in Access as numbers with decimal values, where
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
the Timestamp format of 29-Jun-05 8

04 AM.

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

Regards,

#3 (**permalink**) 08-Nov-2005, 12:52 PM

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.).
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
>
> If your criteria is written like this:
>
> Between [DateFrom] And [DateTo]
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
> 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/ex...tributors.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

04 AM.
>
> Can you please point me in the right direction to make this easier to handle.
>
> Regards,
>

#4 (**permalink**) 08-Nov-2005, 12:52 PM

Tom Wickerath wrote:
> Date/Time values are stored in Access as numbers with decimal values, where
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834

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

Here another suggestion (untested)

BETWEEN INT(CDATE([DateFrom]))
AND INT(CDATE([DateTo])) + TimeSerial(23, 59, 59)

#5 (**permalink**) 08-Nov-2005, 12:53 PM

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:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
represents the time.

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

Here another suggestion (untested)

BETWEEN INT(CDATE([DateFrom]))
AND INT(CDATE([DateTo])) + TimeSerial(23, 59, 59)

#6 (**permalink**) 08-Nov-2005, 12:53 PM

Tom Wickerath wrote:
> While I may have made some minor
> inconsequential mistakes due to the time of day (after midnight when I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
> 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"?

#7 (**permalink**) 08-Nov-2005, 12:53 PM

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/ex...tributors.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
> __________________________________________
>
> "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

04 AM.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
>
> Can you please point me in the right direction to make this easier to handle.
>
> Regards,
>

#8 (**permalink**) 08-Nov-2005, 12:53 PM

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.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
Tom
____________________________________________

"peregenem@jetemail.net" wrote:

A fellow pedant, eh?

Can you tell me why you think "values are stored in
Access"?

#9 (**permalink**) 08-Nov-2005, 13:10 PM

Hi Mark,

Sorry, I didn't notice your second post due to the unnecessary noise
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
generated by "peregenem". Try this form, which makes use of the built-in
DateAdd function to add 1 day:

>=[DateFrom] And

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
_______________________________________________

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6834
"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?