Help designating a description to a number

[Shawn12]

I have a scheduling unit that associates a number to a particular name.

In this example Break=66 and Lunch=76.

What I would like to do is setup something that will tell me "which break". Like "Break 1" and "Break 2"

Here is what I have to work with:

Emp# ID# Code StartMin Desc
6666 5555 66 870 Break
6666 5555 76 1005 Lunch
6666 5555 66 1150 Break


I tried this thinking it might designate it based on the StartMin:

Desc: IIf(

=66 And First([StMin]),"Break 1",IIf([Code]=66 And Last([StMin]),"Break 2","Lunch"))

However this still just shows each as "Break 1". Any ideas would be great. Thanks.

 

[Golom]

Try

Desc: IIf([Code]=66 And [StMin]=Min([StMin]),"Break 1",
      IIf([Code]=66 And [StMin]=Max([StMin]),"Break 2",
      "Lunch"))

Your code is giving those results because [blue]First([StMin])[/blue] and [blue]Last([StMin])[/blue] are being evaluated as Booleans where zero is FALSE and any other number is TRUE.

I suspect though that your query may need to be a bit more elaborate because First, Last, Min and Max are aggregate functions and that implies that you will need a Group By clause if you are using non-aggrated fields in your query.

 

[Shawn12]

Still just says "Break 1" for both breaks. Heres the full code if it helps.

SELECT SchHdrActHdrCombinedToday.Date, SchHdrActHdrCombinedToday.EmpID, SchHdrActHdrCombinedToday.SchID, scheddetail.EXCEPTION_CODE AS Code, scheddetail.START_MINUTE AS StMin, scheddetail.LENGTH AS Length, IIf(

=66 And [StMin]=Min([StMin]),"Break 1",IIf([Code]=66 And [StMin]=Max([StMin]),"Break 2","Lunch")) AS [Desc]
FROM SchHdrActHdrCombinedToday INNER JOIN scheddetail ON (SchHdrActHdrCombinedToday.CUSTOMER_ID = scheddetail.CUSTOMER_ID) AND (SchHdrActHdrCombinedToday.SchID = scheddetail.SCHED_ID)
WHERE (((SchHdrActHdrCombinedToday.CUSTOMER_ID)=1))
GROUP BY SchHdrActHdrCombinedToday.Date, SchHdrActHdrCombinedToday.EmpID, SchHdrActHdrCombinedToday.SchID, scheddetail.EXCEPTION_CODE, scheddetail.START_MINUTE, scheddetail.LENGTH
ORDER BY scheddetail.START_MINUTE;