Holiday Dates

[MANDI201]

Hi this will probably be a very easy one for you clever people but I am having problems with my Holiday Database. I can get it to add up the amount of days that people have off until some bright spark wanted 1/2 a day off! tried putting in an am/pm field but then I would have to put the holidays in daily and not a week at a time.

Hopefully there will be some "magic" available that can help me

Thanks for your time

MANDI

 

[Mile-O]

If you had an extra field (Yes/No) in your table you could call this HalfDay and when it is clicked this means that the holiday is a half day.

Then, when summing holidays you can add the sum of all the full days to the sum of all the half days divided by two using the yes/no property of the field as a criteria when counting.

i.e.

Sum of Full Days + (Sum of Half Days / 2)

 

[MANDI201]

DATES

Thanks for your help but when i added the tick box to my table it allowed me to tick the box but then when I added the same box to my FORM it wont allow me to tick the box? I am totally confused now.

Any suggestions be gratefully received? think Im going mad

MANDI

 

[Mile-O]

You'd have to set the ControlSource of the checkbox on your form to that of the Yes/No field in your table.

 

[MANDI201]

In Controlsource I do have set to Half Day? but still wont let me tick it. Think I must have my thick head on today? sorry to be such a pain. Y cant staff be sensible and have WHOLE days off!

MANDI

 

[Mile-O]

Can you post a sample?

 

[MANDI201]

Thanks for your help... I went in and removed the new Half Day field from the query and re-added it again and low and behold the form now lets me tick the boxes! The form calculates the amount of days taken on the form in form footer can I modify this to make it take into account the half days (now that the box ticks ok)

Currently reads:

=[Forms]![record holidays].[Form]![text20]-(Sum([days taken]))

This works and gives me a correct figure

Thanks

MANDI

 

[Mile-O]

I'm guessing that [Forms]![record holidays].[Form]![text20] is the number of holidays a person has in their holiday entitlement.

So what you'll need to do is look at the sum of days taken and reduce that for some cases where a day might actually be a half day and if the half day is ticked count that as ½.



=DCount("[Half Days]", "tblYourHolidays", "[HalfDays] = False") + (DCount("[Half Days]", "tblYourHolidays", "[Half Days] = True")/2)


or something like that

 

[MANDI201]

Dates

Tried your suggestions but it kept giving me the wrong figures?

Can Access not determine "if empty" add a figure or "if not null" - 0.5 from my total?

Would like something like

Days taken[date_end]-[date_start]+1-if [half_day] is not null then -0.5 if [half_day]=null then -0


In english I would like to minus 0.5 from my total figure only if the half day box has been ticked- hope u can understand this.

I would be grateful for any help as my brain has gone into siezure as I am not "formulated" enough to cope with this! aghh

Thanks MANDI