How to calculate 'half day'

[Ayiramala]

Hi,


I will much appreciate it if someone can help me on this:
On this db, the users input a FROM date and a TO date to apply for leave, and the number of days required is calculated. I was able to get this done, but the problem is if they want leave for only half a day. The term that we usually use is FN and AN. What I want is this: If they choose FN to FN, or AN to AN, we should get .5. If they select FN to AN or AN to FN that is one day. Saturdays, Sundays and certain other days are holidays. On Table2 I have a FROM field and a TO field, but no field for AN/FN (yet). How can we accomplish this?
Thanks.

[June7]

What do AN and FN mean?

Unless you include time components in the FROM and TO data, will need a field in table to indicate a half day choice.

[Ayiramala]

What do AN and FN mean?

Unless you include time components in the FROM and TO data, will need a field in table to indicate a half day choice.

Sorry, I forgot to explain. By FN I mean 'the forenoon session' and AN, 'the afternoon session'. FROM and TO are date fields.

[June7]

Need at least 1 field to indicate half day must be considered in calc. Then reference field in conditional IIf() expression.

[Ayiramala]

Thanks for the reply. But this is my situation: Next to the FROM and TO date fields, I have two identical combo boxes, each with values FN and AN. When a user picks a date, he also must choose either FN or AN from these boxes. So, if he selects FN in both boxes, that should be considered as a half day. Similarely AN. But if he chooses FN in the first and AN in the second, that is a full day. I have FROM, TO, and the two FN/AN fields on the table.

[June7]

The IIf expression in query could be like:

HalfDay: IIf(([FromFA]="FN" AND [ToFA]="FN") OR ([FromFA]="AN" AND [ToFA]="AN"), 0.5, 0)

Use that constructed field in another expression to subtract the calculated value.

[Ayiramala]

That worked! Thank you very much.
There is one minor problem, though. If the FROM is AN and the TO is FN of the next day, the number of days shown is one more than the required one. Other than that, it works fine.
Can we solve this last one too?
Thanks.

[June7]

Sorry, don't understand. Why is it 'required one'?

Date/Time ranges that cross into another period (day, week, month, year) can be problematic.

[Ayiramala]

It is like this: If I select 14/01/2015 AN as the FROM date and 15/01/2015 FN as the TO date, the number of days I get is 2. Not 1, which is what I should get(second half of the first day + first half of the second day = one whole day).
But everything else is just fine. If the first field is FN, and the second is AN, no matter on which day, I get the right number. So also with FN to FN, and AN to AN. The only problem is if the first date has to be AN, and the second, FN of the next day.

[June7]

Maybe another condition in the expression.

IIf(([FromFA]="FN" AND [ToFA]="FN") OR ([FromFA]="AN" AND [ToFA]="AN"), 0.5, IIf([FromFA]="AN" AND [ToFA]="FN",1,0))

[Ayiramala]

Maybe another condition in the expression.

IIf(([FromFA]="FN" AND [ToFA]="FN") OR ([FromFA]="AN" AND [ToFA]="AN"), 0.5, IIf([FromFA]="AN" AND [ToFA]="FN",1,0))

That did it! Now it is perfect!
Thanks for all the time and patience.
Please mark the thread 'solved'. I can't see an option to do it myself.

[June7]

Thread Tools drop down above first post. Done.