dates in a query

[Guest]

how do u change what access thinks of as a "day?" our system here treats
orders after 4:00 PM as next day, and before 4:00 PM as same day. when
running the query for a month i need the orders received allotted by how many
received each "day." (orders counted for Feb.1-orders taken before 4:00 PM;
orders for Feb.2-after 4:00 PM Feb.1 and before 4:00 PM on Feb.2, etc.). days
must be in the format of after 4:00 PM corresponding to next day. now its
using standard days which isnt what we need

 

[Guest]

I am assuming you have one field that has both the date and time stored. If
so, you can add this expression to your query:

IIf(TimeValue([date_time_field])>#4:00:00
PM#,DateAdd("d",1,DateValue([date_time_field])),DateValue([date_time_field]))

This will show the next day for any orders after 4:00 and then you can use
this value to group your report by.

 

[Larry Daugherty]

Hi Grayson,

Access doesn't think. If it did, we might be able to train it rather than
program it. I'd hate to think what we might need for motivation.

You have to decide what falls in a given day. It seems you have a
bothersome chore ahead of you. First, I'd go back to the powers that be and
clearly establish whether there is an actual need to replace a fairly
universal artificial construct with a very local artificial construct based
on the first one. People's feelings might get hurt but it is worth the
trip. The fact that you've posted the issue implies that the "real" dates
and times are entered with some initiating and/or terminating events and
converted to the "Local" convention.

If you can't get the movement you need then you'll have to give it some
thought and create your own algorithms to do the job. Caution, always store
the real dates and times and always calculate the "local" dates, never store
them. If you approach it that way at the outset then you'll do it right and
you'll have a clean application. If you simply jump in and solve the
problem in place when you first run into it you'll tend to create a solution
optimized for that problem and that's awkward everywhere else. Worse, you
might create a different solution each time you need to calculate again.

The basic (well BASIC too) calculation is fairly easy - A fairly simple
function follows. Be warned, this is air code.

Private function MakeMyDay(RealDate as variant, RealTime as variant) as
variant
' you could put any special handling you might need for week-ends and
holidays.
' You may have enough things to test that you'd want to use a Select Case
procedure
' instead of a simple If statement.

' Pass both RealDate and RealTime but return just the Local.value
' Add some error handling

if ([RealTime] < 16:00 then 'check for week-end in the If clause
MakeMyDay=DateAdd("d", 1, [RealDate])
else
MakeMyDay=[RealDate])
end if

end function

HTH

 

[Guest]

works well larry. just one problem. i am not aware of how to check for
weekends, which is a problem because orders do fall on fridays--which would
be scheduled for monday (after 4:00 on friday). this would complete my local
artificial construct which does go against the grain of the universal
artificial construct called time that we adhere our lives to. but, it is now
"lunch time" and i must adhere or pay the consequences to this universal
construct. any further help with the weekend problem is greatly appreciated.

Hi Grayson,

Access doesn't think. If it did, we might be able to train it rather than
program it. I'd hate to think what we might need for motivation.

You have to decide what falls in a given day. It seems you have a
bothersome chore ahead of you. First, I'd go back to the powers that be and
clearly establish whether there is an actual need to replace a fairly
universal artificial construct with a very local artificial construct based
on the first one. People's feelings might get hurt but it is worth the
trip. The fact that you've posted the issue implies that the "real" dates
and times are entered with some initiating and/or terminating events and
converted to the "Local" convention.

If you can't get the movement you need then you'll have to give it some
thought and create your own algorithms to do the job. Caution, always store
the real dates and times and always calculate the "local" dates, never store
them. If you approach it that way at the outset then you'll do it right and
you'll have a clean application. If you simply jump in and solve the
problem in place when you first run into it you'll tend to create a solution
optimized for that problem and that's awkward everywhere else. Worse, you
might create a different solution each time you need to calculate again.

The basic (well BASIC too) calculation is fairly easy - A fairly simple
function follows. Be warned, this is air code.

Private function MakeMyDay(RealDate as variant, RealTime as variant) as
variant
' you could put any special handling you might need for week-ends and
holidays.
' You may have enough things to test that you'd want to use a Select Case
procedure
' instead of a simple If statement.

' Pass both RealDate and RealTime but return just the Local.value
' Add some error handling

if ([RealTime] < 16:00 then 'check for week-end in the If clause
MakeMyDay=DateAdd("d", 1, [RealDate])
else
MakeMyDay=[RealDate])
end if

end function

HTH
--
-Larry-
--

how do u change what access thinks of as a "day?" our system here treats
orders after 4:00 PM as next day, and before 4:00 PM as same day. when
running the query for a month i need the orders received allotted by how many
received each "day." (orders counted for Feb.1-orders taken before 4:00 PM;
orders for Feb.2-after 4:00 PM Feb.1 and before 4:00 PM on Feb.2, etc.). days
must be in the format of after 4:00 PM corresponding to next day. now its
using standard days which isnt what we need

 

[Larry Daugherty]

Hi Grayson,

Look in Access Help for DATE and TIME functions. In it you'll find how to
determine the day of the week. If your week begins on Sunday then if the
new LocalDate becomes Saturday then bump up your LocalDate to be the
following Monday. I'll leave the details to you.

HTH
--
-Larry-
--

works well larry. just one problem. i am not aware of how to check for
weekends, which is a problem because orders do fall on fridays--which would
be scheduled for monday (after 4:00 on friday). this would complete my local
artificial construct which does go against the grain of the universal
artificial construct called time that we adhere our lives to. but, it is now
"lunch time" and i must adhere or pay the consequences to this universal
construct. any further help with the weekend problem is greatly appreciated.

Hi Grayson,

Access doesn't think. If it did, we might be able to train it rather than
program it. I'd hate to think what we might need for motivation.

You have to decide what falls in a given day. It seems you have a
bothersome chore ahead of you. First, I'd go back to the powers that be and
clearly establish whether there is an actual need to replace a fairly
universal artificial construct with a very local artificial construct based
on the first one. People's feelings might get hurt but it is worth the
trip. The fact that you've posted the issue implies that the "real" dates
and times are entered with some initiating and/or terminating events and
converted to the "Local" convention.

If you can't get the movement you need then you'll have to give it some
thought and create your own algorithms to do the job. Caution, always store
the real dates and times and always calculate the "local" dates, never store
them. If you approach it that way at the outset then you'll do it right and
you'll have a clean application. If you simply jump in and solve the
problem in place when you first run into it you'll tend to create a solution
optimized for that problem and that's awkward everywhere else. Worse, you
might create a different solution each time you need to calculate again.

The basic (well BASIC too) calculation is fairly easy - A fairly simple
function follows. Be warned, this is air code.

Private function MakeMyDay(RealDate as variant, RealTime as variant) as
variant
' you could put any special handling you might need for week-ends and
holidays.
' You may have enough things to test that you'd want to use a Select Case
procedure
' instead of a simple If statement.

' Pass both RealDate and RealTime but return just the Local.value
' Add some error handling

if ([RealTime] < 16:00 then 'check for week-end in the If clause
MakeMyDay=DateAdd("d", 1, [RealDate])
else
MakeMyDay=[RealDate])
end if

end function

HTH
--
-Larry-
--

how do u change what access thinks of as a "day?" our system here treats
orders after 4:00 PM as next day, and before 4:00 PM as same day. when
running the query for a month i need the orders received allotted by

how
many

received each "day." (orders counted for Feb.1-orders taken before

4:00
PM;

orders for Feb.2-after 4:00 PM Feb.1 and before 4:00 PM on Feb.2,

etc.).
days

must be in the format of after 4:00 PM corresponding to next day. now its
using standard days which isnt what we need