Date Range with Timestamp

[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,

 

[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
__________________________________________

:

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,

 

[Guest]

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

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
__________________________________________

:

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,

 

[peregenem]

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)

 

[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.

_________________________________________

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)

 

[peregenem]

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"?

 

[aaron.kempf]

INT(DATE what you NEED to do is to subtract half a day-- to always
force it to round down

INT(CDATE([DATEFROM]-.5))

that should work for you

-aaron

 

[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?

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
__________________________________________

:

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,

 

[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
____________________________________________

:

A fellow pedant, eh? Can you tell me why you think "values are stored in
Access"?

 

[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
_______________________________________________

:

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?

 

[aaron.kempf]

a) dont use MDB for anything anywhere
queries are flaky like this and just crap out randomly.
b) dont use MDB for anything anywhere
queries are flaky like this and just crap out randomly.
a) use ADP for real SQL power
while keeping your existing forms, reports, macros, most of your
vba... and DAPs in ADP rock!!

 

[Tom Wickerath]

Aaron = Don Quixote
ADP’s and DAP’s = Dulcinea

_______________________________________

(e-mail address removed) wrote:

a) dont use MDB for anything anywhere
queries are flaky like this and just crap out randomly.
b) dont use MDB for anything anywhere
queries are flaky like this and just crap out randomly.
a) use ADP for real SQL power
while keeping your existing forms, reports, macros, most of your
vba... and DAPs in ADP rock!!