Code to accept 1/2 days (Access 2003/SP3)

[steveh]

Hi all

way out of my depth using Access. I spent the princley sum of £5 to buy an Access staff holiday planner that is actually quite good and I am able to to change the tables and code etc.

Currently the original Access only allowed for full days and the Tables were set to accept only 1, I have gone in and changes these to 2 and have added new descriptions to the Reason table so I now have H (Holiday) or hd (1/2 day Holiday), S (Sick) or hs (1/2 day Sick) etc. and I have adjusted were necessary all of the column widths on the forms to accomodate this but I am stumped as to how to change the report to accept 1/2 days.

Looking at the code I suspect that this may be the function that controls the days.

Code: Select all

Function MyWorkDays(Df As Date, Dt As Date) As Integer
' counts days excluding weekends and special dates
Dim n As Date, MyCount As Integer
MyCount = 1
For n = Df To Dt
    If Weekday(n, vbMonday) < 6 Then
    MyCount = MyCount + 1
        If CountSpecDates(n) = 1 Then
        MyCount = MyCount - 1
        End If
    End If
Next
MyWorkDays = MyCount - 1
End Function

I have looked at the code behind the form that updates the leave but cannot see anything that mentions The leave letters that can be choosen to see if I can change the code (H, S, B, T, M, A, U) and I have looked at all of the code behind the reports but can't see any clues there.

Any help / insight would be greatly appreciated

BTW the Access template is designed by Chris Mead

[HansV]

The MyWorkdays function is the equivalent of the NETWORKDAYS function in Excel. It calculates the number of working days (Mon-Fri) between two dates.

I think we'd have to see (a copy of) the database. Zip the copy and attach the zip file to a reply.

[steveh]

The MyWorkdays function is the equivalent of the NETWORKDAYS function in Excel. It calculates the number of working days (Mon-Fri) between two dates.

I think we'd have to see (a copy of) the database. Zip the copy and attach the zip file to a reply.

Hi Hans

Thank you for looking at the first post. I have attached a ZIP which I hope is readable, there is one user in there with the initials SH (me).

I have just got a message that 7z is not allowed and that is theonly one of 3 Zip programs I have tried that will get it below the 256kb lounge limit. I will try it again from home with something else.

[steveh]

The MyWorkdays function is the equivalent of the NETWORKDAYS function in Excel. It calculates the number of working days (Mon-Fri) between two dates.

I think we'd have to see (a copy of) the database. Zip the copy and attach the zip file to a reply.

Hi Hans

Thank you for looking at the first post. I have attached a ZIP which I hope is readable, there is one user in there with the initials SH (me).

I have just got a message that 7z is not allowed and that is theonly one of 3 Zip programs I have tried that will get it below the 256kb lounge limit. I will try it again from home with something else.

Even using Win Zip 16 I can only get it down to 316kb, I have tried now with about 5 different Zip programs, would it be acceptable to delete all of the forms and just leave the codes in place to try and reduce it?

[HansV]

Yes, let's try that first.

[steveh]

Yes, let's try that first.

Hi Hans

No joy I am afraid, I first removed all of the forms which made little difference, I have now deleted all of the reports and the queeries and this is the best I can get

Compress.gif

I will try some other ZIP programs today, can you advise what extensions are acceptable? (As mentioned 7z zip took it straight down to about 137kb but cannot be posted to the lounge)

[HansV]

Steve, I've sent you a PM.

[HansV]

Hi Steve,

I received your database.

As far as I can tell, everything already works OK, but after entering leave data, you have to click "5. Compile data for reports" on the Main Form. This fills the table used by the LeaveRept1 report. If you omit that step, the report won't be up-to-date.

x181.png

As you see, the half day codes are nicely displayed.

Another problem is to count the days. I'd add a number (Double) field RCount to the Reasons table:

x182.png

Add this field to the FullLeaveDetails query.
In the FullLeaveDetailsForUser query, change the definition of WorkDaysAbsent to

WorkDaysAbsent: myworkdays([ldatefrom],[ldateto])*[RCount]

You now get workdays multiplied by 0.5 for half days:

x183.png

The LeaveInits2 report will pick this up automatically:

x185.png

[HansV]

By the way, if you want to attach a database in the future, it's best to compact it before zipping it. In Access 2003: Tools | Database Tools | Compact and Repair Database.
Using this, I reduced the size of the database to 732 KB and that of the zip file to 112 KB, well within the 250 KB limit for the Lounge:

x186.png

[steveh]

By the way, if you want to attach a database in the future, it's best to compact it before zipping it. In Access 2003: Tools | Database Tools | Compact and Repair Database.
Using this, I reduced the size of the database to 732 KB and that of the zip file to 112 KB, well within the 250 KB limit for the Lounge:

x186.png

Hi Hans

Fantastic, thank you very much, took me a while to find where to put the query definition not having looked in anger at an Access db since I joined the lounge when I was working in Singapore about 2000.

This has re whetted my appetite so stand by for more dumb questions!!