Calculation depends on day of week

[Jim]

I have a query that shows the total hours scheduled for
each date. The query is the control source for my
report. On the report I added a text box to calculate
the number of hours available based on a calucation. IIF
([SchedHours]>0,Sum(12-[SchedHours]), 12)

There are 12 hours allowed per day. If there are any
hours scheduled, the formula is 12 minus the scheduled
hours to show the remaining hours that are still
available. If there are no scheduled hours, then
available hours is 12.

Now my group has decided to have 20 hours available
Tuesday through Thursday but still 12 hours on Monday and
Friday. I could really use help with the formula for
this. Would it be best to do this in the query or on the
report? I'd like one report that shows the availability
for a rolling 28 day period (which I have now), that can
also determine the availability depending on whether the
date is a Mon, Tues, Wed, Thur or Fri.

Thanks for any suggestions you can offer!
Jim

 

[tina]

-----Original Message-----
I have a query that shows the total hours scheduled for
each date. The query is the control source for my
report. On the report I added a text box to calculate
the number of hours available based on a calucation. IIF
([SchedHours]>0,Sum(12-[SchedHours]), 12)

There are 12 hours allowed per day. If there are any
hours scheduled, the formula is 12 minus the scheduled
hours to show the remaining hours that are still
available. If there are no scheduled hours, then
available hours is 12.

Now my group has decided to have 20 hours available
Tuesday through Thursday but still 12 hours on Monday and
Friday. I could really use help with the formula for
this. Would it be best to do this in the query or on the
report? I'd like one report that shows the availability
for a rolling 28 day period (which I have now), that can
also determine the availability depending on whether the
date is a Mon, Tues, Wed, Thur or Fri.

Thanks for any suggestions you can offer!
Jim
.
generally speaking, it's better to do calculations as

close to the source as is feasible. if you can create a
calculated field in the query without compromising other
report design requirements, then do it in the query.

if each record in your query has a field showing a date,
and another field showing the total scheduled hours for
that date, then you can add a calculated field to the
query, as

IIf(DatePart("w",[SchedDate]) In (3,4,5),20-
[SchedHours],12-[SchedHours])

this expression should work in an unbound report control
also, if you need to put it there instead.

you might want to read up on the DatePart function (also
DateAdd and DateDiff functions) in Access Help. they can
be invaluable when you're manipulating dates in your
database.

 

[Jim]

Thanks, Tina. This is exactly what I was looking for. I
will try it first thing Monday. Thanks so much!

Jim

-----Original Message-----

-----Original Message-----
I have a query that shows the total hours scheduled for
each date. The query is the control source for my
report. On the report I added a text box to calculate
the number of hours available based on a calucation. IIF
([SchedHours]>0,Sum(12-[SchedHours]), 12)

There are 12 hours allowed per day. If there are any
hours scheduled, the formula is 12 minus the scheduled
hours to show the remaining hours that are still
available. If there are no scheduled hours, then
available hours is 12.

Now my group has decided to have 20 hours available
Tuesday through Thursday but still 12 hours on Monday and
Friday. I could really use help with the formula for
this. Would it be best to do this in the query or on the
report? I'd like one report that shows the availability
for a rolling 28 day period (which I have now), that can
also determine the availability depending on whether the
date is a Mon, Tues, Wed, Thur or Fri.

Thanks for any suggestions you can offer!
Jim
.
generally speaking, it's better to do calculations as

close to the source as is feasible. if you can create a
calculated field in the query without compromising other
report design requirements, then do it in the query.

if each record in your query has a field showing a date,
and another field showing the total scheduled hours for
that date, then you can add a calculated field to the
query, as

IIf(DatePart("w",[SchedDate]) In (3,4,5),20-
[SchedHours],12-[SchedHours])

this expression should work in an unbound report control
also, if you need to put it there instead.

you might want to read up on the DatePart function (also
DateAdd and DateDiff functions) in Access Help. they can
be invaluable when you're manipulating dates in your
database.
.

 

[Steve Schapel]

Jim,

First of all, to be honest I can't really make head nor tail of your
expression. Where does the Sum come into it? If I assume your
description is correct, i.e. one record per day, and if I assume that
if SchedHours is not >0 then it must be =0, then the expression should
simply be...
=12-[SchedHours]
If your design is such that SchedHours could be Null, then use...
=12-Nz([SchedHours],0)

OK, now for the 20 or 12 question. Use the Weekday() function to work
with the day of the week. This will give it to you...
=12-[SchedHours]-8*(Weekday([YourDateField],3)<4)

As regards where to put it, the pedantic answer is that it is
better/quicker/more efficient to put it in the report rather than the
query. The practical answer is that unless you had a very large
number of records, you wouldn't notice any difference, and it doesn't
really matter.

- Steve Schapel, Microsoft Access MVP