Days of week

[FB1]

Hi Everyone

CR 11
MySQL

Have a report where a person attends 2-3 appoinments every week, they must attend on the correct days of the week
ie Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.

I have 3 fields with the relevant day in Shift1, shift2, and shift3. ( if they only attend twice then only shift 1 and 2 are populated) I have added these up to show either 2 or 3 days to attend.

I have created a field that converts the appointment day into a day of the week called dayname

I have created a formular that looks at the dayname against the shifts
@ correct day
(if {Command.dayname} like ({Command.shift1full} ) then 'correct'
else
if {Command.dayname} like ({Command.shift2full} ) then 'correct'
else
if {Command.dayname} like ({Command.shift3full} ) then 'correct'

else
'Wrong Shift')

I then have a formular to add up the wrong days
@Wrong day)
(if {Command.dayname} like ({Command.shift1full} ) then 0
else
if {Command.dayname} like ({Command.shift2full} ) then 0
else
if {Command.dayname} like ({Command.shift3full} ) then 0

else
1)

I have the report group by person then grouped on the appointment day by week.

with a sum on how many appointment days in that week, also a sum on @Wrong day against that week

Then simple just put both together and this will give me the wrong shift if the person has attended a wrong shift, but i need to know if they have missed at all, the problem starts when the 1st or 2nd of that month is mid week and likewise when the end of the month is mid week.
how do I compensate for the this.

thank you for all your time, this is most appreciated

 

[Madawc]

I'd do the whole thing differently. Make a boolian:

{Command.dayname} like ({Command.shift1full} ) 
or {Command.dayname} like ({Command.shift2full} )
or {Command.dayname} like ({Command.shift3full} )

This would come out True or False, and you can reference it elsewhere. Count on that basis rather than doing the same test twice. Also watch out for nulls, then stop a formula with blank output.

Incidentally, ToText includes a simple option for the data name. Look it up on HELP, if you don't use it already.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP