Re: Duane: Calendar report by hours over 2 days
From: Michael Noblet (anonymous_at_discussions.microsoft.com)
Date: 07/16/04
- Next message: rusty: "suspend footer printing based on grouping."
- Previous message: Marshall Barton: "Re: Report Page Length Limitation"
- In reply to: Duane Hookom: "Re: Duane: Calendar report by hours over 2 days"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 16 Jul 2004 10:27:24 -0700
Cool thanks. I was just trying to make sure that was a
working idea before I spent the time
>-----Original Message-----
>Mike,
>You would need to use similar expressions. I don't have
the time to work
>through the exact syntax but it should work after some
trial and error. I
>would be particularly careful to not hard-code your times
into the
>expressions.
>
>--
>Duane Hookom
>MS Access MVP
>--
>
>"Michael Noblet" <anonymous@discussions.microsoft.com>
wrote in message
>news:2d7bf01c46a77$82aaca60$a401280a@phx.gbl...
>> Duane,
>>
>> That worked great. Would this be the best way to split
>> times into blocks? If I am trying to to calulate the
>> usage for a block of 7am to 3 pm and then 3pm to 5pm.
If
>> the procedure runs from 2pm to 4 pm, I need to split it
>> between the 2 groups.
>>
>> I would assume the same approach would work, but I could
>> use a bit of help on the calculations in the SQL
statement.
>>
>> Mike
>> >-----Original Message-----
>> >Look slike that should work
>> >>-----Original Message-----
>> >>You are absolutely on the right track. I just figured
>> out
>> >a no code
>> >>solution.
>> >>1) create a table tblDayNums with a single numeric
field
>> >[DayNum] and two
>> >>records with values 0 and 1
>> >>2) Create a query with this SQL. You will need to
>> >substitute your own table
>> >>and field names. I used:
>> >> tblSchedule
>> >> ==============
>> >> SchDate
>> >> SchStartTime
>> >> SchEndTime
>> >> SchProcedure
>> >> SchRoom
>> >>SELECT [SchDate]+[DayNum] AS ScheduleDate,
>> >tblSchedule.SchStartTime,
>> >>tblSchedule.SchEndTime, IIf([SchEndTime]<
[SchStartTime]
>> >And
>> >>[DayNum]=1,#12:00 AM#,[SchStartTime]) AS
CalcStartTime,
>> >>IIf([SchEndTime]<[SchStartTime] And [DayNum]=0,#11:59
>> PM#,
>> >[SchEndTime]) AS
>> >>CalEndTime, tblSchedule.SchRoom,
>> >tblSchedule.SchProcedure, tblDayNums.DayNum
>> >>FROM tblDayNums, tblSchedule
>> >>WHERE (((tblDayNums.DayNum)<=Abs([SchEndTime]<
>> >[SchStartTime])));
>> >>
>> >>ScheduleDate, CalcStartTime and CalcEndTime should be
>> >used in the report.
>> >>
>> >>--
>> >>Duane Hookom
>> >>MS Access MVP
>> >>--
>> >>
>> >>"Michael Noblet" <anonymous@discussions.microsoft.com>
>> >wrote in message
>> >>news:2b3a801c468d5$d4e078a0$a601280a@phx.gbl...
>> >>> I am thinking that I would need to break the record
up.
>> >>>
>> >>> Case starts on 06/01/2004 at 23:30 and ends on
>> 6/2/2004
>> >at
>> >>> 01:30.
>> >>>
>> >>> currently the data is populated with a start date,
>> start
>> >>> time and end time. I was thinking that I would
>> truncate
>> >>> the origanal record to show 23:30 to 23:59 and then
>> >have a
>> >>> new record with a start date of 6/2/2004 fro 00:00
to
>> >>> 01:30.
>> >>>
>> >>> I would prefer to do this in the query, but would be
>> >just
>> >>> as comfortable doing it with code. Any thoughts on
a
>> >>> procedure to make this happen or am I heading down
the
>> >>> wrong path.
>> >>>
>> >>> >-----Original Message-----
>> >>> >You would have to make sure the lngDuration value +
>> >>> lngStart don't add up to
>> >>> >greater than your day. Do you think you need to
break
>> a
>> >>> record into two
>> >>> >records if it crosses a day end? You would need to
do
>> >>> this in the code or
>> >>> >query. I would certainly not change the way you
add or
>> >>> edit records.
>> >>> >
>> >>> >--
>> >>> >Duane Hookom
>> >>> >MS Access MVP
>> >>> >
>> >>> >
>> >>> >"Michael Noblet"
<anonymous@discussions.microsoft.com>
>> >>> wrote in message
>> >>> >news:2bd7101c468cf$c029a450$a401280a@phx.gbl...
>> >>> >> I have a calendar report that I have designed
based
>> >on
>> >>> the
>> >>> >> crew rotation schedule example in Duane Hookom's
>> >>> calendar
>> >>> >> database. I am creating a utilization report of
>> >>> operating
>> >>> >> rooms and I am grouping by date then by tim and
>> then
>> >by
>> >>> or
>> >>> >> room. The problem I run into is that I have
>> >procedures
>> >>> >> that start on one day and run into the next.
>> >>> >>
>> >>> >> surgery starts at 23:30 and goes until 01:30.
The
>> VB
>> >>> >> script with the formating function that draws the
>> >>> calendar
>> >>> >> line does not like that. How can I get around
the
>> >>> >> overlapping day problem?
>> >>> >>
>> >>> >>
>> >>> >
>> >>> >
>> >>> >.
>> >>> >
>> >>
>> >>
>> >>.
>> >>
>> >.
>> >
>
>
>.
>
- Next message: rusty: "suspend footer printing based on grouping."
- Previous message: Marshall Barton: "Re: Report Page Length Limitation"
- In reply to: Duane Hookom: "Re: Duane: Calendar report by hours over 2 days"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
