Querying database for employees with X hours of sick leave

Could someone, anyone, help me and compose a VBA script for me to use with MS Access?

I know nothing about MS Access of VBA scripting for it but really need some help.

I have an MS Access database on a server at work that records employee sick leave, by hour, by date.

I need to know the number of occurences when employees were gone on sick leave for 10 or more consecutive work days, during a calendar year that starts on Jan 1 and ends on Dec 31.

Employees work 7.5 hours in a day, Monday to Friday, no weekends.

I was thinking I need a script that runs a check for 75 hours of sick leave in each 14 day spread during the year.

If I counted right, there should be 255 such spreads: Starting with Jan 1 to 14, then Jan 2 to 15, ... and ending with Dec 18 to 31.

If an employee is gone for 30 consecutive work days, the result should only show him once. But if he goes 10 work days, comes back to work for 5 work days, then goes for another 10 work days, the result should show him twice.

I hope this makes sense and that someone can rise to the challenge and put this together.

Thank you

Erik Lupien

ADezii

Expert

3,770 Posts

July 15th, 2008
06:15 PM
#2

Re: Querying database for employees with X hours of sick leave

Erik, I think this problem is a little more complicated than you realize, at least in my interpretation of it. If you are not in any great rush I'll be glad to have a look at it, but first some preliminary information.

What is the Name of the Back End Database on the Server?
Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
What is the Table Name containing the relevant Fields?
What are the Field Names, and their respective Data Types?
Am I correct in interpreting your request to mean you are looking for all occurrences of Sick Leave for 10 or more consecutive days? A single period of 20 or more days would still constitute a single occurrence?

Erik Lupien

Newbie

18 Posts

July 15th, 2008
07:19 PM
#3

Re: Querying database for employees with X hours of sick leave

Hi there, I'm not in a rush at all. I would greatly appreciate this help. And I make no illusion that this is complex. I started to think about it and got lost when I tried to figure it out.

The answers to your questions are below, I hope they are what you were looking for.

What is the Name of the Back End Database on the Server?
The MDB is called LEAVE 2008
Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
It is an Access database.
What is the Table Name containing the relevant Fields?
Table name is LEAVE 2007-2008
What are the Field Names, and their respective Data Types?
Field Name is L* (long integer number)
The Values you want are 2100 (uncertfied sick leave) and 2200 (certified sick leave)
The Hours column is called CATSHOURS (double number)
The Date Field is called WORKDATE (date field)
Am I correct in interpreting your request to mean you are looking for all occurrences of Sick Leave for 10 or more consecutive days? A single period of 20 or more days would still constitute a single occurrence?
Yes you are correct.

Thank you again,

Erik

ADezii

Expert

3,770 Posts

July 16th, 2008
01:36 AM
#4

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by Erik Lupien

What is the Name of the Back End Database on the Server?
The MDB is called LEAVE 2008
Are we talking about an Access Database or Project, where the Back End Database resides on an SQL Server?
It is an Access database.
What is the Table Name containing the relevant Fields?
Table name is LEAVE 2007-2008
What are the Field Names, and their respective Data Types?
Field Name is L* (long integer number)
The Values you want are 2100 (uncertfied sick leave) and 2200 (certified sick leave)
The Hours column is called CATSHOURS (double number)
The Date Field is called WORKDATE (date field)
Am I correct in interpreting your request to mean you are looking for all occurrences of Sick Leave for 10 or more consecutive days? A single period of 20 or more days would still constitute a single occurrence?
Yes you are correct.

Thank you again,

Erik

I will be very busy in the next few days, but I'll have a look at it as soon as I can. In the meantime, kindly post some data that would represent no Sick Leave, at least 10 days of Sick Leave, then a few days of no Sick Leave again. I need to visually see what constitutes Sick Leave usage and what determines when it is continuous. Kindly post the relevant Field Names along with the data. Will see you later.

Erik Lupien

Newbie

18 Posts

July 16th, 2008
04:02 PM
#5

Re: Querying database for employees with X hours of sick leave

Hi there,

Below is what I believe you asked for.

I think you may also want to note holidays, which pose an added challenge.

In the table below, the date 2007-09-03 (a Monday) was a holiday. It was not worked by the employee who was on paid holiday leave. There is no leave record for this activity on this table. But the employee was sick and away for a least 5 working days before that date and at least 5 working days after it (and therefore, at least 10 working days in and around that date).

Holidays in 2007 this far to note:
2008-01-01
2008-03-21
2008-03-24
2008-05-19
2008-01-01

SAMPLE OF THE TABLE, AFTER DATA IS PULLED FROM THE DATABASE

PERNR = Employee Number
WORKDATE = Date the employee was off
SKOSTL = Centre to which the cost is assigned
LSTAR = Field through which leave code is pulled from the database
AWART = Field through which leave code goes into the database
LAEDA = Date leave was input into system
STATUS = Indication of whether or not the leave was approved by the manager
CATSHOURS = Duration in hours of the leave
ALLDF = Was leave all day, not compulsary
LTXA1 = Description of entry, not compulsary
TYPE = Shortcut for leave codes, not consistently used

PERNR WORKDATE SKOSTL LSTAR AWART LAEDA STATUS CATSHOURS ALLDF LTXA1 TYPE
91697378 2007-08-08 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-09 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-10 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-13 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-14 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-15 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-16 126347100 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-17 126347101 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-20 242313501 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-21 242313501 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-22 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-23 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-24 242313501 2100 2100 2007-08-30 30 0 21
91697378 2007-08-27 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-28 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-29 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-30 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-08-31 242311300 2100 2100 2007-08-30 30 7.5 X Sick leave 21
91697378 2007-09-04 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-05 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-06 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-07 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-10 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-11 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-12 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-13 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-14 242311300 2100 2100 2007-09-18 30 7.5 X Sick leave 21
91697378 2007-09-17 242311300 2100 2100 2007-09-18 30 0 21
91697378 2007-09-18 242311300 2100 2100 2007-09-18 30 0 21

Good luck!

Erik

ADezii

Expert

3,770 Posts

July 16th, 2008
11:29 PM
#6

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by Erik Lupien

Erik, one more question - what specifically indicates a Sick Leave Day?

[ALLDF] = X
[LTXA1] = Sick Leave
[ALLDF] = X AND [LTXA1] = Sick Leave
Something different - please specify

Erik Lupien

Newbie

18 Posts

July 18th, 2008
12:47 PM
#9

Re: Querying database for employees with X hours of sick leave

Hi there,

LSTAR has many other codes, such as codes for bereavement leave, vacation leave, family-related leave, etc. The two sick leave codes for LSTAR are 2100 (which refers to uncertified sick leave, which means we didn't ask for a doctor's note) and 2200 (which refers to certified sick leave, which means we asked for a doctor's note).

Each row in the table I presented to you refers to a single day. The only way to calculate sick leave for 10 continous days is to count if there are 75 hours of sick leave (10 days x 7.5 hours per day).

A script would have to have a start date, e.g. Jan 1, and an end date, e.g. Jan 14 (making sure the 2 dates are 14 days apart), then check if there are 75 hours of sick leave within those date.

If there is a vacation day in those 14 days, the script would have to check for 67.5 hours of sick leave (9 days x 7.5 hours per day) plus 7.5 hours of vacation time (which is code 1120 - sorry I had not told you this before as I just realized it would be helpful in this way).

The script would then have to do all this again for the 2 week range starting on Jan 2 and ending on Jan 15, and continue to Jan 3 ending Jan 16, and so on until Dec 18 to Dec 31.

I think there are 255 10-day cycles that include weekdays only in a year (this means weekends are not included in the 10-day count). So I think there should be script would probably loop 255 times.

It becomes interesting when the script has to factor in the following possibility, given the way we record the sick leave. If someone is gone for 20 days, say from Jan 1 to Jan 20. That person would appear in the result for Jan 1 to 14, jan 2 to 15, jan 3 to 16 ... and so on until jan 7 to 20; so a total of 7 times, but we would only want to see a result of 1 time for this person.

I hope this helps. I may be off on the way to script this. I only my logic above as an example to demonstrate what I need as an end result.

Thank you again and cheers,

Erik

ADezii

Expert

3,770 Posts

July 20th, 2008
01:22 PM
#10

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by Erik Lupien

I did some preliminary work on your Project, but it may be too extensive to post. Download the Attachment, review the code and logic thoroughly, then get back to me whenever you can.

Attached Files

Need a Script.zip (32.2 KB, 25 views)

Erik Lupien

Newbie

18 Posts

July 22nd, 2008
01:59 PM
#11

Re: Querying database for employees with X hours of sick leave

Hi again,

Wow, this is impressive - thank you so very much.

I've noted something that needs to be fixed, otherwise my results are inaccurate. The 2-week intervals around Easter weekend and the Christmas Holidays have 2 holidays.

Looking at the holiday table you created, I thought the solution could be to add another column titled "Holidays" and to have boxes to check for each 2 week interval, as is the case for the Holiday column?

I could then either check boxes in the Holiday or the Holidays column, not both, to reflect that the 2-week interval has either 1 holiday or 2 holidays. I would hope that the script could then check for 75 hours of consecutive sick leave in the 2 week intervals (when no boxes are checked in either column), or 67.5 hours (when boxes are checked in the Holiday column) or 60 hours (when boxes are checked in the Holidays column).

Lastly, could the holidays interval table start April 1, 2007 and end March 31, 2008, to reflect the fiscal year? And when going from 2007 to 2008 I think there are a few 2-week intervals required, e.g. Dec 19 to Jan 2, Dec 20, to Jan 3, etc. all the way to Dec 31, to Jan 13.

I looked at the VBA code, thinking maybe I can do this but it is way more sophisticated than what I remotely aware of working with so I'm really at your mercy here!

I hope you don't mind tweaking this.

Thank you again.

Erik Lupien

Newbie

18 Posts

July 22nd, 2008
07:54 PM
#12

Re: Querying database for employees with X hours of sick leave

I just noticed another thing, having played with the holiday table and generating the sick leave data.

In the example below, the one employee is gone from August 8 to September 16. Is it possible for the VBA script to figure out that this is the same employee gone for one absence and only report it once, rather that report it 13 times as is the case here?

Thanks again and I look forward to your response.

Erik

Employee Start Date End Date Holiday in Interval? Total Sick Hours
91697378 2007-08-08 2007-08-21 0 75
91697378 2007-08-09 2007-08-22 0 75
91697378 2007-08-24 2007-09-06 -1 67.5
91697378 2007-08-25 2007-09-07 -1 67.5
91697378 2007-08-26 2007-09-08 -1 67.5
91697378 2007-08-27 2007-09-09 -1 67.5
91697378 2007-08-28 2007-09-10 -1 67.5
91697378 2007-08-29 2007-09-11 -1 67.5
91697378 2007-08-30 2007-09-12 -1 67.5
91697378 2007-08-31 2007-09-13 -1 67.5
91697378 2007-09-01 2007-09-14 -1 67.5
91697378 2007-09-02 2007-09-15 -1 67.5
91697378 2007-09-03 2007-09-16 -1 67.5

ADezii

Expert

3,770 Posts

July 23rd, 2008
12:05 AM
#13

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by Erik Lupien

Hello Erik, download the Attachment and see that several items were taken care of.

Quote:

Originally Posted by

In the example below, the one employee is gone from August 8 to September 16. Is it possible for the VBA script to figure out that this is the same employee gone for one absence and only report it once, rather that report it 13 times as is the case here?

Therein lies a problem, when I get a chance I'll look into it more deeply. When you fill all your Holidays in, post them so I am working with the same Interval Table. If possible, it would also be easier to work with the actual data that you are referring to.

Attached Files

Need a Script_2.zip (48.3 KB, 21 views)

Erik Lupien

Newbie

18 Posts

July 23rd, 2008
03:37 PM
#15

Re: Querying database for employees with X hours of sick leave

Hi Stewart,

Thank you for the reminder.

And thank you also to ADezii.

I have taken your reminder and what ADezii provided me and built on it myself.

For example, there are, at it turns out up to 3 holidays in a 2 week interval if looking at the Dec 24 to Jan 7 2-week interval. I've managed to amend the files myself.

What I'm struggling with at this point is how to eliminate from the results table entries for the same employee that has been gone for 15 days but would show up 2 times (once for each of the 2 14-day interval in those 15 days - day 1 to 14 and day 2 to 15).

I'm going to give this some thought and try my hand and hopefully get it working, otherwise I hope I might get some guidance/direction once more.

Erik

PS - how do I attach my new file if and when I need to?

ADezii

Expert

3,770 Posts

July 23rd, 2008
11:49 PM
#16

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by Erik Lupien

This Link should hopefully point you in the right direction, if not, let me know.
Attachments

Erik Lupien

Newbie

18 Posts

July 25th, 2008
01:30 PM
#17

Re: Querying database for employees with X hours of sick leave

Status update ...

I modified the 2-week intervals table and added a column "Interval" in which I number each interval in a sequence from 1 to 353.

I also changed the script so that the results table displays the 2-week interval's interval nmber (1 to 353).

I extracted from my leave database all sick leave entries for fiscal 2007 to 2008 for employees with more than 52.5 hours of sick leave. There were more than 400,000 entries. FYI, there were more than 1.5 million entries in total for sick leave (big organization with many employees).

I deleted 399,000 and ran a test on the remaining 1,000 entries and the "Generate Sick Leave Data" script worked beautifully.

I'm now running the script on the entire 400,000+ entries .... clicked the button more than 24 hours ago and it's still running.

Next step ...

Roghly the results table looks something like this ...

Interval # Employee Start date End date Sick leave
(1-353) # of interval of interval hours
1 333444 01-Jan-07 14-Jan-07 67.5
2 333444 02-Jan-07 15-Jan-07 75
3 333444 03-Jan-07 16-Jan-07 75
4 333444 04-Jan-07 17-Jan-07 75
7 333444 07-Jan-07 23-Jan-07 75
8 333444 08-Jan-07 24-Jan-07 75
9 333444 09-Jan-07 25-Jan-07 75
10 333444 10-Jan-07 26-Jan-07 75
3 555666 03-Jan-07 16-Jan-07 75
4 555666 04-Jan-07 17-Jan-07 75
5 555666 05-Jan-07 18-Jan-07 75
6 555666 06-Jan-07 19-Jan-07 75
7 555666 07-Jan-07 20-Jan-07 75
11 555666 11-Jan-07 25-Jan-07 75
12 555666 12-Jan-07 26-Jan-07 75
13 555666 13-Jan-07 27-Jan-07 75

I now need to run a script against this table to generate a new table that would look something like what follows (I want to try writing it because I want to respect Stewart's post to me above and don't want to impose on anyone) but would greatly appreciate some guidance on the logic to help me start,

Employee Start date End date Sick leave
# of leave of leave hours
333444 01-Jan-07 17-Jan-07 292.5
333444 07-Jan-07 26-Jan-07 300
555666 03-Jan-07 20-Jan-07 375
555666 11-Jan-07 27-Jan-07 225

In keeping with earlier posts in this thread, I think the logic for this second script that I need goes something like this:

Starting at top of table ...
A - for first employee's first entry in the table
B - check to see if the next entry (the second) is for same employee
C - if it is, lookup this first entry's 2-week interval #
D - lookup the second 2-week interval #
E - check to see if the second 2-week interval # is 1 unit
greater than the first 2-week interval #
F - if it is, check to see if the next (third) entry is for the
same employee
G - if it is, lookup the third 2-week interval #
H - check to see if the third 2-week interval # is
1 unit greater than the second 2-week
interval #

repeat cycle until able to find the last unbroken sequential 2-week interval # for the same employee

G - when the "next entry" is not for same employee post in a results table:
the employee #, the start date of leave, the end date, the sick leave
H - when the first and last sequential entries are found for the same
employee
I - count the total sick leave for all 2-week intervals from the first to
the last inclusive
J. lookup the start date of leave in the first entry
K. lookup the end date of leave in the last entry
L. post in a results table:
the employee #, the start date of leave in the first entry, the end date of
leave in the last entry, the sick leave counted at I, above.

Does this make sense?

Thank you, Erik.

ADezii

Expert

3,770 Posts

July 25th, 2008
03:10 PM
#18

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by Erik Lupien

Don't do anything drastic at this point. I've called upon some of the more experienced and skilled Members for a method to derive the information you need from tblFinalResults, eliminating the contiguous intervals. Be patient and let's see what developes.

Erik Lupien

Newbie

18 Posts

July 25th, 2008
05:40 PM
#20

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by nico5038

Nice problem :-)
In a similar "find the period/gap" case I've used a + and - string to get the problem solved.

Approach:
1) Create an initial string for the whole period e.g. of 365 times the '-' character.
2) Process all records and update the day position of sick leave with a "+"
(No problem to set the same day twice or more times, just use the start and end date of each row)
3) Get the results by using INSTR(strYear,"++++++++++") and working your way through the string from left to right.

Getting the idea ?

Nic;o)

Sorry, could you elaborate on this or provide an example to help visualize what you mean?

Thanks, Erik

ADezii

Expert

3,770 Posts

July 25th, 2008
11:13 PM
#21

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by nico5038

Hello Nico, I would also like to see a concrete example of your approach, since I am having trouble visualizing it also.

NeoPa

Administrator

10,165 Posts

July 26th, 2008
01:59 PM
#22

Re: Querying database for employees with X hours of sick leave

Subscribing - and hopefully clarifying the last point a little.

I believe the idea, within a specific time-frame (a year for instance), is to build up a string with a character (-) in each position to indicate a normal day.

Code: ( text )

Dim strYear As String
strYear = String(365, "-")

The position of the character within the string is the mapping of which day in the year it represents.

Processing through the records you have for an employee, you can replace each "-" with a "+" for each day they are absent.

Simplifying for a week (strWeek starts on the Sunday), if an employee had Wednesday and Thursday off for that week, the value in strWeek (line #2) would be :

Code: ( text )

=SMTWTFS
"---++--"

ADezii

Expert

3,770 Posts

July 26th, 2008
11:18 PM
#23

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by NeoPa

Code: ( text )

Dim strYear As String
strYear = String(365, "-")

Code: ( text )

=SMTWTFS
"---++--"

Thanks for the clarification, NeoPa.

Erik Lupien

Newbie

18 Posts

July 30th, 2008
07:56 PM
#25

Re: Querying database for employees with X hours of sick leave

Hi there,

I'm sorry it's taken a while to respond.

Believe it or not, my computer is still crushing the data. It's been going at it since 9:00 a.m. last Thursday morning (July 24). My system hasn't crashed and the task manager tells me MSACESS.exe is using anywhere from 55 to 65% of my computer's CPU resources (it changes every second or so between those percentages) - so I'm guessing it's still processing the data.

Having said all this, I haven't had the opportunity to try the new code yet!

Assuming I'm not wrong on Access still processing (if someone here has something to say about it please so), I will let it continue. And as soon as it's done, I will let you know.

As for the code, I added entries to the leave 2007-2008 table to show employee 99765426 sick for the entire month of February 2008 and ran the updated script on another computer ... here's what it generated:

Code: ( text )

Employee Start Date End Date Sick Hours Interval#
12345678 ===> 08-10-2007 ===> 08-23-2007 =====> 67.5 =======> 145
12345678 ===> 08-16-2007 ===> 08-29-2007 =====> 67.5 =======> 151
12345678 ===> 08-18-2007 ===> 08-31-2007 =====> 67.5 =======> 153
12345678 ===> 08-20-2007 ===> 09-02-2007 =====> 67.5 =======> 155
55555555 ===> 08-08-2007 ===> 08-21-2007 =====> 75.0 =======> 143
55555555 ===> 08-10-2007 ===> 08-23-2007 =====> 67.5 =======> 145
99765426 ===> 04-01-2007 ===> 04-14-2007 =====> 67.5 =======> 001
99765426 ===> 04-01-2007 ===> 04-14-2007 =====> 67.5 =======> 001
99765426 ===> 05-08-2007 ===> 05-21-2007 =====> 60.0 =======> 038

As you can see, it didn't show employee 99765426 sick the entire month of February 2008 or at all during that month. Any idea why?

About the VBA code that could find a break in the 2-week intervals for an employee ... what do you think about the idea of creating two arrays, one with the numbers I have, and another one with the numbers expected, then looping through them and compare each value, if they aren't equal it's 'broken'. Something like this:

Code: ( text )

Dim i&,x&
Redim arExpected(353)
For i=0 to UBound(arExpected)
x=x+1:arExpected(i)=x
Next

PS I will attach the updated file in the next post.

Erik

Last edited by NeoPa : July 31st, 2008 at 12:22 PM. Reason: Please use the [CODE] tags provided

nico5038

Moderator

1,999 Posts

July 30th, 2008
08:32 PM
#27

Re: Querying database for employees with X hours of sick leave

Hi Erik,

Just click on the Edit/Delete link of the comment after posting, it will allow the placement of an attachment.

I see I missed some comments from Neopa And ADezii about the +/- approach.

A small sample to show how it works for a two week period searching for 3 day's sick leave:
Input:

Code: ( text )

John 1/1/2008 1/5/2008
John 1/9/2008 1/11/2008

Processing by creating two weeks initially in strPeriod:

Code: ( text )

------------

After first row:

Code: ( text )

+++++---------

After second row:

Code: ( text )

+++++---+++---

Now using Instr(strPeriod,"+++") will return 1 as being the first leave of three days.
To check for multiple leaves you can test:

Code: ( text )

IF InstrRev(strPeriod,"+++") = Instr(strPeriod,"+++") then
' only one found as we find the same start position
else
' multiple processing necessary
endif

The multiple processing can be done using a switch between testing for "+++" and "-" (when a period in between leaves can be only one day, you could also use more by enlarging the string)
The test for the end of the period in the sample would be:

Code: ( text )

Instr(Instr(strPeriod,"+++"),strPeriod,"-")

This will return the value 6.
That can be used for the startpos in Instr(startpos,strPeriod,"+++") to find the next leave, etc..

Bit clearer ?

Nic;o)

ADezii

Expert

3,770 Posts

July 31st, 2008
12:10 AM
#28

Re: Querying database for employees with X hours of sick leave

Quote:

Originally Posted by nico5038

Code: ( text )

John 1/1/2008 1/5/2008
John 1/9/2008 1/11/2008

Processing by creating two weeks initially in strPeriod:

Code: ( text )

------------

After first row:

Code: ( text )

+++++---------

After second row:

Code: ( text )

+++++---+++---

Now using Instr(strPeriod,"+++") will return 1 as being the first leave of three days.
To check for multiple leaves you can test:

Code: ( text )

IF InstrRev(strPeriod,"+++") = Instr(strPeriod,"+++") then
' only one found as we find the same start position
else
' multiple processing necessary
endif