The answer is of course yes. In his book, "Alpha Five -Xbasic for Everyone", Peter Wayne wrote a nifty "User Defined Function" to accomplish such for version 3 and 4. Keep that option open because it should work just as well in version 5.
My close friend runs a program for a local public school system that tracks data on student suspensions using Access. For reasons totally unrelated to this article, she isn't happy with Access and asked if I could create an application to track school suspensions in Alpha Five. Is my name Robert? Of course I can [LOL].
All of the current data was exported from Access into Alpha Five [version 5], and for the most part, to maintain some level of consistency with the original database, we did not rename the data fields. There are only 3 fields that are of interest for this article, they are:
| Name of Field | Type of Field | Description |
| Date_Removed | Date | Date the student was suspended |
| Date_Return | Date | Date the student returned to school |
| Days_Removed | Numeric | Number of Days between those 2 dates |
Days_Removed began life as a simple calculated field:
[Date_Return] - [Date_Removed]
That calculation worked fine until I was advised that including school holidays and weekend days in the total were distorting the final figure. Duhhhhh Robert, you should have figured that out from day one. Obviously my little calculated field was not adequate for this task. OK, I figured it was time to utilize Peter Wayne's excellent script. But just then, my long time partner-in-crime, Steve Workings, told me that Alpha Software included a brand new function in version 5 that appeared to be perfect for our needs.
The new function is bus_days_between()
This function works in conjunction with the Holidays system table that ships with Version 5. Although you can create your own Holidays table, for most people, using the built-in table is probably the best and easiest solution. Just enter all of the relevant holidays for your business into the system table. This new function counts the number of calendar days between the two dates and then subtracts the total number of weekend days and holidays.
So you ask, where can I find the Holidays table? Do the following:
| • | Open your database |
| • | On the Main Menu at the top of the screen, click on View & then Settings |
| • | If you can't see all of the folders, click on the plus sign next to System |
| • | Click on the Holidays Folder |
| • | An XDialog box will pop up allowing you to enter / edit holidays in the table. Enter all of the appropriate holidays, and make any necessary changes. Now its time to create a Field Rule for the Days_Removed numeric field. |
1. Selecting the system Holidays Table
In order to enter a field rule, you must close the active form and go to the Alpha Five Control Panel. Once you are in the Control Panel
| • | Click on the Tables/Sets Tab |
| • | Find the table where you want to create a Field Rule [in our example: tblAES] |
| • | Right Click on tblAES, then select Edit Field Rules |
| • | In the Field Rules Dialog Box, click on the symbol next to the Field Names |
| • | Select the Field where you want to create a rule. In our case that is the Days_Removed field. Remember, we want to count the number of days so it must be a numeric field. |
2. Field Rules, selecting the Days_Removed field
We want to create an expression that will calculate the number of days between the two relevant dates, Date_Removed and Date_Return. We will create that expression using the Bus_Days_Between() function. The field rule is:
BUS_DAYS_BETWEEN(DATE_REMOVED,DATE_RETURN, "YES")
Click OK and Save the Field Rules change.
As described earlier, this expression will calculate the number of calendar days between those two dates and then subtract the total number of weekend days and holidays. Weekend days are day numbers 1 & 7 of the week, but how will the function know which days are holidays? Remember, we just added our holidays to the Alpha Five system Holiday table.
I tested out the expression by adding a couple of new student records, entering dates for both fields, Date_Removed and Date_Return, and this terrific little function worked perfectly. However, I noticed new problems with the data, and at first, I couldn't figure out what was wrong and why it was happening?
There were some negative 12-digit numbers showing up in the Days_Removed field and I wondered why they were there. When I tried to delete those numbers, a dialog box popped up saying "End Date cannot come before Start Date", and I wasn't allowed to delete the number. Hmmmm, that was strange, I'm almost positive that was one of the error checks written into the new function. In addition, one cannot edit calculated fields.
Next I tried to Re-evaluate the field rules, but it wouldn't work. I couldn't see anything but the Windows hourglass until I eventually closed down Alpha Five. This happened repeatedly and it was the final clue in this little puzzle. It revealed the problem was related to the brand new field rule I created using the Bus_Days_Between() function.
It's only a guess, but I think the Alpha developers assumed this function would always compare two different, but real dates, as the basis for the calculation which determines the number of workdays. When you think about it, that makes perfect sense since companies, who are in business for a profit, have a specific need for that type of calculation. Obviously they are not going to try and compare one real date with a blank date field.
However, our public school application deviated somewhat from the intended purpose of the function. Student suspension dates [Date_Removed] are always entered, but in general, the data-entry person doesn't know when the student will complete his or her suspension and return to the public school system. Therefore, the Date_Return field is usually left blank, and those blank fields caused some wacky calculations, throwing Alpha off balance. Let me remind the reader, that weird behavior didn't happen when both dates were entered into the system. In that scenario, the function worked as intended.
In light of the above, we had to modify the field rule expression so it would comply with our slightly different methodology. Once again, with the help of my partner-in-crime, Steve Workings, I came up with a very simple solution. I went back and added error checks to the field rule. The updated expression first checks for a blank Date_Removed and then for a blank Date_Return field. If either field is blank, the result of the calculation is zero [0].
If there are dates entered into both fields, then the Bus_Days_Between() function kicks in and does it's thing. Here is a view of the updated field rule expression:
3. Changing the Days_Removed Field Rule Expression
Reading the expression in the graphic above is a little difficult, so I will display it below. Please note, two French brackets are used to designate a blank date field.
IF(Date_Removed = {},0, IF(Date_Return = {}, 0, Bus_Days_Between(Date_Removed, Date_Return, "YES") Here's the English translation.
| • | If Date_Removed is blank, enter a zero in the Days_Removed field. |
| • | If Date_Return is blank, enter a zero in the Days_Removed field. |
| • | Otherwise, the Bus_Days_Between() function calculates the total number of school days between the two dates. |
This modification to the Field Rule resolved all of our problems. Now we could re-evaluate field rules without locking up the application. And, there are no more 12-digit negative numbers showing up in the Days_Removed field.