How to Convert Case and Calculate Total Time

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Jan-1997 >

How to Convert Case and Calculate Total Time

Ken Getz            
 
This month, Ken digs in on two questions: creating a "smart" proper case conversion function and calculating total elapsed time, given a series of time values. He supplies answers (though they may be different answers) in both Access 2.0 and in Access 95/97 (the 32-bit database is in Access 95 format, but can easily be converted for Access 97). The 16-bit demonstration database (QA970116.MDB) and the 32-bit version (QA970132.MDB) are in download file GETZ39.EXE.
 
I need to be able to convert text into proper case as I type it. That is, I want the first letter of every word converted to uppercase and the rest to lowercase. I found a procedure somewhere that does what I need -- almost. It just blindly converts all the text. But I want something a bit smarter about proper names, and it must not convert small articles ("a," "and," and "the," for instance). Is there something I'm missing? There's got to be a way to do this!
 
This is such a common problem, I'm surprised that the question doesn't come up more often! I've written one or two proper-case conversion routines myself, but nothing I've written is as "smart" as you require. Of course, if you're using Access 95 or Access 97, you already have the built-in StrConv VBA function, which will do just what a brute-force code solution would do: It converts the first letter of every word to uppercase, and forces the rest of each word to lowercase. This doesn't help much for articles, or for handling proper names. To write code to accomplish what you need would be prohibitively difficult, but if you were to look up the exceptions to the rules in a table, your routine could work a bit better than through code alone.
 
It's this alternative that I'm going to suggest to you. Last year, I happened to save a piece of code posted to the MSACCESS CompuServe forum that does what you need. I've modified it quite a bit for publication here, but its original author, Emmanuel Soheyli (75333.1003@compuserve.com) did a nice job of putting together a very concise procedure that handles a great number of cases. This procedure, ProperLookup, walks through the text you pass it, building up "words" of alphabetic characters. As soon as it finds a non-alphabetic character, it checks out the most current word it's collected, and looks it up in a table. If it's there, it uses the text it finds in the table. If not, it performs a direct conversion of the word to proper case. The code then moves through the rest of the text, continuing the process. Once it hits the end of the string, it handles the final word and returns the result. The following code listing shows the Access 2.0 version of the code, although the 32-bit code (for Access 95 and Access 97) is quite similar:
 

Dim db As Database

Dim rst As Recordset

    

Declare Function IsCharAlphaNumeric Lib "USER" _

 (ByVal intChar As Integer) As Integer

 

Function ProperLookup(strIn As String, _

 fForceToLower As Integer) As Variant

 

  ' Suggested by code posted to CompuServe's 

  ' MSACCESS forum by Emmanuel Soheyli (75333,1003)

    

  Dim strOut As String

  Dim strWord As String

  Dim intI As Integer

  Dim strC As String

    

  Set db = CurrentDb()

  Set rst = db.OpenRecordset("tblSpecialCase", _

   DB_OPEN_TABLE)

  rst.Index = "PrimaryKey"

    

  strOut = ""

  strWord = ""

 

  If fForceToLower Then

    strIn = LCase(strIn)

  End If  

 

  For intI = 1 To Len(strIn)

    strC = Mid$(strIn, intI, 1)

    If IsCharAlphaNumeric(Asc(strC)) Or strC = "'" 

      Then strWord = strWord & strC

    Else

      strOut = strOut & FixWord(strWord) & strC

      strWord = ""

    End If

  Next intI

 

  ' Process the final word.

  strOut = strOut & FixWord(strWord)

    

  ' Close the table and return the result.

  rst.Close

  ProperLookup = strOut

End Function

 

Function FixWord(ByVal strWord As String) As String

  ' "Properize" a single word.

 

  Dim strOut As String

    

  If Len(strWord) > 0 Then

    rst.Seek "=", strWord

    If rst.NoMatch Then

      strOut = UCase(Left(strWord, 1)) & Mid(strWord, 2)

    Else

      strOut = rst!Name

    End If

  End If

  FixWord = strOut

End Function

 
 
Figure 1 shows a sample form, frmProperLookup, which converts text to proper case as you type. You might want to give it a try to get a feel for what the code is doing. For example, try typing this: "the store sells ibm computers to mr. mcgregor and his brother." As you type, the code will convert the text to the proper case, looking up each word to find the exceptions.

199701_kg1
Figure 1

 
 
The first portion of ProperLookup sets up the lookup table. Because both the rst and db variables are declared in the module's Declarations area, they're available to any procedure in the module until they're explicitly closed. Because the FixWord procedure needs the recordset (once for every word), it makes sense to open the recordset once for each call to ProperLookup, instead of once for each call to FixWord. Here's the first portion of the procedure:
 

Set db = CurrentDb()

Set rst = db.OpenRecordset("tblSpecialCase", _

 DB_OPEN_TABLE)

rst.Index = "PrimaryKey"

 
 
To make this code run as fast as possible, you must be able to use the Seek method on the recordset based on the lookup table, and that requires a table-type recordset. In order to create a table-type recordset, the table must be local, or you'll need to modify the code to explicitly open the database containing your lookup table so you can create a table-type recordset based on the lookup table.
 
If you've specified that you want all words forced to lowercase (except for the first letter of each word), the procedure calls the built-in LCase function to convert the entire string to lower case. This saves it from having to deal with each character as it comes along:
 

If fForceToLower Then

  strIn = LCase(strIn)

End If

 
 
The next chunk of code does the bulk of the work. This loop works its way through the entire string, one character at a time. For each character, it decides if it should add the character to the word it's collecting (if the character is alphabetic or an apostrophe), or if it should stop and handle the word it's already gathered (if the character is anything except a character). If the character isn't part of the word, the code calls the FixWord function to perform the conversion. (You'll note that the code uses the IsCharAlphaNumeric API function, rather than testing for any specific range of characters. Not only is this the fastest way to check for alphabetic characters, but it'll also work in countries that use characters outside the AZ range in their words. You should never check for characters between A and Z, unless all you're concerned with is those particular characters. It's a big world out there.) Here's the code:
 

For intI = 1 To Len(strIn)

  strC = Mid$(strIn, intI, 1)

  If IsCharAlphaNumeric(Asc(strC)) Or strC = "'" Then

    strWord = strWord & strC

  Else

    strOut = strOut & FixWord(strWord) & strC

      strWord = ""

  End If

Next intI

 
 
One problem: The loop doesn't trigger the FixWord function for the final word! As designed, the loop calls the FixWord function only when it finds a character that isn't alphabetic. That's not the issue now -- you've just plain run out of characters! The solution, then, is to call FixWord one more time once you've dropped out of the loop, which then covers the final word. FixWord handles empty strings fine, so if there wasn't a final word, it'll just return an empty string. Finally, the procedure closes the recordset it's opened and returns the output string:
 

' Process the final word.

strOut = strOut & FixWord(strWord)

 

' Close the table and return the result.

rst.Close

ProperLookup = strOut

 
 
The interesting question, of course, is how FixWord does its work. This routine first searches for the word in the lookup table. Remember, searches through Access data are always case-insensitive, so if it finds a match, it returns the word it found table. If it can't find a match, it returns the word with the first letter converted to uppercase. (Previous steps have converted the rest of the letters to lowercase, if that's what you told the procedure to do.)
 
To use this technique in your own applications, follow these steps:
 
1. Import basProperLookup.
 
2. Import tblSpecialCase (or create your own table -- just make sure you've got a field named Names that is set as the Primary Key field). Add rows as necessary to handle words you capitalize in a special way (the sample table in download file GETZ39.EXE includes a few proper names and some simple articles).
 
3. Call ProperLookup, passing it two parameters: the text to be converted and a Boolean (True/False) value indicating whether you want the procedure to force the text to lowercase before converting the first letter of each word to uppercase. (In the Access 95/97 version of the code, the second parameter is optional and defaults to True.)
 
What's missing from this solution? First of all, it's not terribly smart. It works only with the specific words you've added to the list. If you've added "McGregor" but not "MacGregor," there's no way for the code to know how to handle the word that isn't there. It isn't possible to work with proper names that contain spaces (such as "de Long," for example), although you could add many of the proper name "prefixes" to the lookup table to avoid incorrect capitalization. The code only checks for alphabetic characters and apostrophes as legal characters in words. You may find you need to add to the list of acceptable characters. In that case, you may want to create a list of acceptable characters as a constant, and use the InStr function to see if strC is in the list. For example, to treat apostrophes and hyphens as valid word characters, you could declare the following constant:
 

Const conWordChars = "'-"

 
 
Then you could modify the check for characters like this:
 

If IsCharAlphaNumeric(Asc(strC)) Or _

 (InStr(conWordChars, strC) > 0) Then

 
 
Thanks to Emmanuel for suggesting this solution. On my (admittedly very fast) machine, this code runs quickly enough to perform the conversion as you type, as in the sample form in download file GETZ39.EXE. For the most part, you'll want to call this code from the AfterUpdate event instead of the Change event, because you won't be displaying the formatted text as users type.
 
I have a table in which I store the hours my employees work every day. If Joe works 8.5 hours, I'm storing 8:30 in a date/time field. I've got a report that prints out the total hours for each employee, but if they work more than 24 hours in a time period (and of course, they always do!), Access rolls over the time so that 25 hours shows up as 1:00. I guess I just don't understand time values! I need to find a way to add time values together and end up with a total. Is there some way to do this?
 
Although I'd sworn off time/date calculation questions after running about six of them in a row when I first started this column, it's time (pun intended) for another! This is certainly a common problem, and it has a moderately simple solution involving only a small amount of program code.
 
First of all, the big misunderstanding is that Access stores only absolute times. It has no way of storing, nor measuring, elapsed times in its Date/Time fields. When you enter 8:30, you may think you're entering the number of hours someone worked, but you're actually entering a specific time: 8:30 a.m. on the date you entered the value. Access has no qualms about performing aggregate calculations on Date/Time fields -- they're stored internally as floating point values, so there's no problem performing the calculation -- but the result won't be what you had in mind.
 
How does Access store dates internally? As I've mentioned before in this column, Access dates are stored as the number of days since December 30, 1899, plus a fractional portion that contains the time on the specified date. For example, on January 15, 1997, at noon, the value for the current time would be 35445.5. Of course, a date representing noon on December 30, 1899 would be 0.5. When Access converts these values for display as actual dates and times, it will never display an invalid time (237:30, for example, which may in fact be the value you need, as the sum of working hours).
 
The task, then, is to find a solution that allows you to enter time values as you've become accustomed, but to convert them to some simple value for calculations, and then format the output as a standard time value for display. The sample database contains tblHours, a table containing hours worked for several employees (Hours is a Date/Time field, formatted as Short Date):
 

ID

Name

Hours

Date

1

Joe

8:30

10/3/96

2

Tom

12:20

10/3/96

3

Mary

1:30

10/4/96

4

Joe

12:00

10/3/96

5

Tom

1:14

10/3/96

6

Peter

8:00

10/1/96

7

Peter

12:00

10/2/96

8

Mike

4:10

10/1/96

9

Mary

7:25

10/2/96

10

Mary

12:30

10/3/96

11

Mary

14:00

10/5/96

 
 
The sample database in download file GETZ39.EXE also includes rptHours, a report that formats the data in a simple summary format. Figure 2 shows Mary's hours, with a summary in the footer. Figure 3 shows the report footer, including a grand total of all the hours.

199701_kg2      
Figure 2

---------

199701_kg3
Figure 3
 
To make this all happen, you'll need the two functions in basElapsedTime: CMinutes and CTimeStr. CMinutes accepts as a parameter a Date/Time value and returns the time portion, converted to the corresponding number of minutes. Given that value, you can easily sum up a series of time values. Then, when you're ready to display your sum, you'll need the CTimeStr function. This one, given a number of minutes, returns a string representing the total, in hh:mm format.
 
For example, to sum three time values and convert that sum back into a time format, you could use an expression like this:
 

CTimeStr(CMinutes(#8:30#) + CMinutes(#12:30#) + _

 CMinutes(#13:25#))

 
 
The result of this expression is the string "34:25".
 
Each of the functions consists of just a few lines of code. The CMinutes function first checks to make sure the value you've sent it is, in fact, a date/time value. Then, it subtracts its integer portion from the entire value and multiplies the resulting fraction by 24*60, resulting in the number of minutes represented by the fractional portion:
 

Function CMinutes (pvarTime As Variant) As Long

  ' Convert a date/time value to the number of 

  ' minutes since midnight (that is, remove the date 

  ' portion and just work with the time part). The 

  ' return value can be used to calculate sums of 

  ' elapsed time.

 

  If VarType(pvarTime) <> V_DATE Then

    CMinutes = 0

  Else

    ' Subtract off the whole portion of the 

    ' date/time value and then convert from a 

    ' fraction of a day to minutes.

    CMinutes = (pvarTime - CLng(Int(pvarTime))) * 24 * 60

  End If

End Function

 
 
CTimeStr, the function that converts the number of minutes back to a string formatted as a time value, is just as simple. It takes the number of minutes and performs an integer division (using the "\" operator) to get the number of hours. Then it uses the Mod operator to find the number of minutes (the remainder when you divide by 60). The function formats each of those values, and concatenates them together as a string return value:
 

Function CTimeStr (plngMinutes As Long) As String

    ' Convert from a number of minutes to a string

    ' that looks like a time value.

    '

    CTimeStr = Format(plngMinutes \ 60, "0") & _

     ":" & Format(plngMinutes Mod 60, "00")

End Function

 
 
The sample report, rptHours (in download file GETZ39.EXE) , uses these functions to calculate and format total hours worked. In the footer section for each employee (the Name Footer section on the report), I've placed a hidden text box, txtSumHours, to calculate the sum of the calculated value. The text box has the following ControlSource property:
 

=Sum(CMinutes([Hours]))

 
 
This text box calculates the sum of the converted minutes for each day's work. To display the total converted to time format, the visible text box in the footer (txtFormattedTime) calls the CTimeStr function with the following ControlSource:
 

=CTimeStr([txtSumHours])

 
 
Access uses the first text box to total the number of minutes for each employee, but because it isn't visible it doesn't show up on the report. The second text box uses the value calculated in the hidden text box and formats its value as a time string. I've used the same technique in the report footer, summing the total number of minutes into a hidden text box (txtTotalHours) and displaying its converted value in txtFormattedTotal.
 
You're not limited to using these functions for reports. They'll work fine in queries, in forms, or called from macros or Access Basic/VBA. Anytime you need to treat time values as elapsed times rather than specific date/time values, these functions will help you out.
 
Read about how to get the download GETZ39.EXE on this page