Date/Time Calculation past midnight...help

[Bec_FS]

Okay, I have a tricky query I need to run, and I am sure there are several
ways to approach this. I have a field called DataCollDate (which is both
date and time) and another field with temperature and wind direction. What I
am trying to do is calculate descriptive statistics on temperature and wind
based on a Date/Time range. Here is the tricky part. I need it to group the
data based on time from 4:10 pm on one day until 7:30 am the next day as One
day and then avg, mean, std on temperature based on that "day", and then keep
repeating that process. (There are 200,000 records in the database and I have
already eliminated the date/times I don't need...7:31 am to 4:09 pm) Any
advice would be greatly appreciated, Bec

 

[Douglas J. Steele]

You should be able to use a couple of calculated fields in your query.

Subtract 970 minutes (the number of minutes between midnight and 16:10) from
DataCollDate to put it all on the same day, then throw away any data where
the time is after 15:20 (which is 970 minutes prior to 07:30).

To subtract 970 minutes, you use DateAdd("n", -970, [DateCollDate]).

To ignore times after 15:20, you create a second calculated field that uses
the TimeValue function on the calculation above, and set a criteria of <
#14:20:00 under that field.

Your query will look something like:

SELECT DateValue(DateAdd("n", -970, [DateCollDate])) AS CalculatedDay,
Field1, Field2
FROM MyTable
WHERE TimeValue(DateAdd("n", -970, [DateCollDate])) < #14:20:00#

You can then use that query as the basis for your calculations.

 

[Bec_FS]

Thanks for your input, I did get this to work...however I have an added
question. Now that I have the date where needs to be, it still see's the
field as data/time...even the I formatted it as date, so when I run the
descriptive stats, it does not give me a unique date for calculation...I
think it is still seeing time.

You should be able to use a couple of calculated fields in your query.

Subtract 970 minutes (the number of minutes between midnight and 16:10) from
DataCollDate to put it all on the same day, then throw away any data where
the time is after 15:20 (which is 970 minutes prior to 07:30).

To subtract 970 minutes, you use DateAdd("n", -970, [DateCollDate]).

To ignore times after 15:20, you create a second calculated field that uses
the TimeValue function on the calculation above, and set a criteria of <
#14:20:00 under that field.

Your query will look something like:

SELECT DateValue(DateAdd("n", -970, [DateCollDate])) AS CalculatedDay,
Field1, Field2
FROM MyTable
WHERE TimeValue(DateAdd("n", -970, [DateCollDate])) < #14:20:00#

You can then use that query as the basis for your calculations.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay, I have a tricky query I need to run, and I am sure there are several
ways to approach this. I have a field called DataCollDate (which is both
date and time) and another field with temperature and wind direction.
What I
am trying to do is calculate descriptive statistics on temperature and
wind
based on a Date/Time range. Here is the tricky part. I need it to group
the
data based on time from 4:10 pm on one day until 7:30 am the next day as
One
day and then avg, mean, std on temperature based on that "day", and then
keep
repeating that process. (There are 200,000 records in the database and I
have
already eliminated the date/times I don't need...7:31 am to 4:09 pm) Any
advice would be greatly appreciated, Bec

 

[Douglas J. Steele]

That's why I included the DateValue function in the sample SQL: DateValue
retrieves just the Date portion of the Date/Time value.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your input, I did get this to work...however I have an added
question. Now that I have the date where needs to be, it still see's the
field as data/time...even the I formatted it as date, so when I run the
descriptive stats, it does not give me a unique date for calculation...I
think it is still seeing time.

You should be able to use a couple of calculated fields in your query.

Subtract 970 minutes (the number of minutes between midnight and 16:10)
from
DataCollDate to put it all on the same day, then throw away any data
where
the time is after 15:20 (which is 970 minutes prior to 07:30).

To subtract 970 minutes, you use DateAdd("n", -970, [DateCollDate]).

To ignore times after 15:20, you create a second calculated field that
uses
the TimeValue function on the calculation above, and set a criteria of <
#14:20:00 under that field.

Your query will look something like:

SELECT DateValue(DateAdd("n", -970, [DateCollDate])) AS CalculatedDay,
Field1, Field2
FROM MyTable
WHERE TimeValue(DateAdd("n", -970, [DateCollDate])) < #14:20:00#

You can then use that query as the basis for your calculations.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay, I have a tricky query I need to run, and I am sure there are
several
ways to approach this. I have a field called DataCollDate (which is
both
date and time) and another field with temperature and wind direction.
What I
am trying to do is calculate descriptive statistics on temperature and
wind
based on a Date/Time range. Here is the tricky part. I need it to
group
the
data based on time from 4:10 pm on one day until 7:30 am the next day
as
One
day and then avg, mean, std on temperature based on that "day", and
then
keep
repeating that process. (There are 200,000 records in the database and
I
have
already eliminated the date/times I don't need...7:31 am to 4:09 pm)
Any
advice would be greatly appreciated, Bec