Help with expression builder for complexed calculation

[astron2012]

Hi I have a database to show the employees schedule. I have a form 'JobDetails' with subform 'JobDetailsSub'.

The mainform has a textbox with a date that is used to bring up the jobs in the subform.

The subform has 5 fields;

Start Date
Start Time
End Date
End Time
HoursToday (unbound textbox, time format)

I want to use the expression builder in the HoursToday field to do the following calculation but I don't have a clue how to set this up.

I have 3 conditions that I need the field to look at;

1) If Forms!JobDetails.TextDate matches the date in StartDate then I want the field to calculate EndTime minus StartTime....... or

2) If Forms!JobDetails.TextDate is after the date in StartDate AND before the date in EndDate then the value of the field should be 08.00....... or

3) If Forms!JobDetails.TextDate is after the date in StartDate BUT equals the date in EndDate then the value should be the time difference between 09.00.00 and EndTime

I'm using Access 2007

I really hope someone can help me with this, I have spent many hopeless hours trying to figure this out!

Or if anyone has any better suggestions on how to perform this action I would be grateful.

Thanks

 

[rammudali]

hi,
you may try "select case" method. case1 - 1 st conditionm case2 - 2nd condition, case3 - 3rd condition. end select