Chris Weber shows you how DateAdd can give you results that you may not be expecting–and then provides the routine that will give you what you want. Along the way he introduces you to the kind of problems that crop up when you do math with the Double datatype.
We all develop applications with a blind faith in the accuracy of Access and VBA’s intrinsic functions. Microsoft has evolved these libraries of procedures and methods for our general use and continues to grow them to meet our development needs. Our faith in this out-of-the-box code is usually well founded, but occasionally you’ll bump into an anomaly, and a crash normally follows a bump. If you use the DateAdd function–or any function that returns a Double datatype, however cleverly hidden–you get unexpected results. I’m going to point out a problem with that very useful function and provide a workaround that solves the problem. You might want to check to make sure that you don’t have a program that’s generating errors due to this “feature.”
I’ve been working on a scheduling program for one of my clients. The basic idea is to match up job candidates with companies with whom they desire interviews. The companies are provided timeslots of various lengths and the trick is to provide a schedule that reconciles the availability of the candidates within the constraints of the available timeslots.
For each company, the user creates the timeslots by entering a series of Begin and End times. Though beyond the scope of this discussion, later on, my super-secret algorithm assigns candidate IDs to timeslots and (hopefully) creates a satisfactory meeting schedule that’s displayed in Excel. The tblCoTimeSlots is shown in Figure 1, filtered for company representative number (CoRepID) 1. The primary key for the table is an AutoNumber, MtgSchedID, which identifies a scheduled meeting/appointment.
One of the features of this system is the ability to move the timeslots for a company forward or backward in 15-minute increments. This is useful when my heuristic can’t come up with a suitable solution because of overlapping meeting conflicts across companies. To move the timeslots, I originally employed the DateAdd function in an update query to add or subtract the requisite amount of time. Figure 2 shows the query that adds one hour to each of Company 1’s timeslots, while Figure 3 shows the results.
Because my users can manually enter the timeslots, there’s always the possibility that they might create overlapping timeslots for a company. This would negate the efforts of my scheduling algorithm. So, I always check first to see if any overlaps occur for any companies. I do this using a correlated query (qryConfilctingTimes) that incorporates a pair of domain functions. The query (shown in Figure 4) contains two calculated fields that use Jet domain functions. In each case, values from the current row are passed in as part of the Where clause, thus correlating the results to the current row. Here’s the text for the calculated fields:
ScheduleConflicts: DCount("MtgSchedID", "tblCoTimeSlots","(MtgSchedID<>" & [MtgSchedID] & ") And (CoRepID=" & [tblCoTimeSlots].[CoRepID] & ") And ((BeginTime>=#" & [BeginTime] & "# AND BeginTime<#" & [Endtime] & "#) Or (EndTime<=#" & [EndTime] & "# AND Endtime>#" & [Begintime] & "#))") ConflictID: DFirst("MtgSchedID", "tblCoTimeSlots","(MtgSchedID<>" & [MtgSchedID] & ") And (CoRepID=" & [tblCoTimeSlots].[CoRepID] & ") And ((BeginTime>=#" & [BeginTime] & "# AND BeginTime<#" & [Endtime] & "#) Or (EndTime<=#" & [EndTime] & "# AND Endtime>#" & [Begintime] & "#))")
The first calculated field (ScheduleConflicts) uses the DCount function to tell me whether there are any overlapping timeslots. It does this by counting the number of MtgSchedIDs for the CoRepID in the current row where either:
- The BeginTime is greater than or equal to the current row’s BeginTime and less than the current row’s EndTime.
- The EndTime is less than or equal to the current row’s EndTime and greater than the current row’s BeginTime.
The second calculated field (ConflictID) uses the DFirst function to return the first MtgSchedID of any overlapping meetings for this current row’s CoRepID. If you look back at the updated data in Figure 3, you can see that qryConflictingTimes shouldn’t return any rows.
If there weren’t any conflicting results appointments before I add one hour to each slot, then there shouldn’t be any conflicts after running qupdWithDataAdd–no matter how many times I run it. However, Figure 5 shows the results of running qryConflictingTimes after running qupdWithDateAdd three times: Some conflicts have been found! And the more I run qupdWithDateAdd, the more conflicts the second query finds. The number of conflicts created rises if I run qupdWithDateAdd passing DateAdd a negative value for the Number parameter in order to subtract one hour.
Behind the scenes
As I observed these unexplainable conflicts, I began to think about what was happening behind the scenes. Dates are stored by Jet and implemented in VBA as Double datatype. In other words, dates are really floating point numbers: a number with an integer and a decimal portion with the decimal portion allowed to be very, very, very small. The integer portion represents the number of days since day zero (Dec 30, 1899) and the decimal portion is the fraction of the day that has passed since time zero (12:00 AM) for the given day. As I thought about the underlying datatype, I had a “Eureka” moment. I suspected that inaccurate fractional additions were being made by DateAdd and were moving increasingly further from the mark each time I ran qupdWithDateAdd. This isn’t an unusual problem with the Double data datatype. One common analogy to working with Double data is that it’s like moving a pile of dirt on a sandy beach: Every time you move the pile, you lose a little dirt and gain a little sand. I decided to take a look at my dates but cast them as numbers, rather than dates, so that I could see the underlying numbers before they’re displayed as dates and times (see Figure 6).
Alas, no such luck. The results in Figure 5 show the values that Jet considers to be overlapping, boxed for easy comparison: MtgSchedID 42 overlaps with 1, 1 overlaps with 5, and 4 overlaps with 45. If you examine the entries in Figure 5 for the overlapping appointments (42, 1, 4), you can see that there shouldn’t be any overlap. At first I thought that the repeating decimals that appear in Figure 6 were the problem, but the terminating decimal 0.625 (which, as 5/8 of the day represents 3 PM) is also causing a conflict.
Nonetheless, I was convinced that DateAdd was appending miniscule portions of time to the end of these numbers and that the QBE grid, JET, or VBA was somehow rounding the results during display. I decided to write my own date manipulation function to solve the problem.
I based my workaround on my earlier assumption that VBA’s DateAdd is making inaccurate fractional additions to the floating-point representation of a date. I decided to avoid the floating-point problems by only using integer math in a pair of new functions: MyDateAdd and DateAddDoIt. If I abandoned the Double datatype and everything worked, it would be a clue that the problem was related to the Double datatype that DateAdd uses and that dates are stored in.
The first function (MyDateAdd) is a wrapper function for the second function (DateAddDoIt) and is called exactly like VBA’s DateAdd. The code figures out what to do based on the Interval parameter (add hours, weeks, and so on) passed to the routine. One warning: In order to use integer math exclusively, this routine only handles whole hours, minutes, and seconds. To ensure that only whole numbers are passed, the Number parameter is a long integer.
Function MyDateAdd(ByVal Interval As String, _ ByVal Number As Long, DateTime As Date) As Date Dim Days As Long Dim Hrs As Long Dim Mins As Long Dim Secs As Long Select Case Interval Case "yyyy", "q", "m", "y", "d", "w", "ww" MyDateAdd = DateAdd(Interval, Number, DateTime) Case "h", "n", "s" Days = Int(CDbl(DateTime)) Hrs = Hour(DateTime) Mins = Minute(DateTime) Secs = Second(DateTime) DateAddDoIt Interval, Number, Days, Hrs, Mins, Secs MyDateAdd = Format(CDate(Days), "mm/dd/yyyy ") & _ CDate(Hrs & ":" & Mins & ":" & Secs) Case Else End Select End Function
If I pass in an Interval of “yyyy”, “q”, “m”, “y”, “d”, “w”, or “ww”, MyDateAdd passes the work off to VBA’s DateAdd. Remember, the days of year, quarter, month, and so forth are all part of the whole number portion of the floating-point date so won’t be affected by any problems with fractions. In fact, VBA’s DateAdd seems to handle these with no problems. If the Interval is an “h”, “n”, or “s”, the Days, Hrs, Mins, and Secs are passed over to my DateAddDoIt function by reference. By passing the data by reference, any changes that I make to the data in DateAddDoIt will be passed back to this routine. Finally, MyDateAdd takes those altered portions and returns a date in the format “mm/dd/yyyy hh:nn:ss”.
The real work is done in DateAddDoIt. A Select Case statement checks the Interval value passed into the routine. In each case, if the Number added to the original value of the Interval exceeds the limit for that unit of measure (that is, 60 seconds to a minute, 60 minutes to an hour, 24 hours to a day), the overflow is extracted. That overflow is recursively passed back in to DateAddDoIt and the Days, Hrs, Mins, and Secs parameters are modified accordingly. If the modulus (the remainder after division) is less than zero, I borrow from the next higher interval and again pass in the Days, Hrs, Mins, and Secs to DateAddDoIt to be updated:
Sub DateAddDoIt(ByVal Interval As String, _ ByVal Number As Long, Days As Long, Hrs As Long, _ Mins As Long, Secs As Long) Dim Overflow As Long Select Case Interval Case "d" Days = Days + Number Case "h" Hrs = Hrs + Number If Hrs >= 24 Or Hrs < 0 Then Overflow = Hrs \ 24 Call DateAddDoIt("d", Overflow, Days, _ Hrs, Mins, Secs) Hrs = Hrs Mod 24 If Hrs < 0 Then ' borrow a day Call DateAddDoIt("d", -1, Days, _ Hrs, Mins, Secs) Hrs = 24 + Hrs End If End If Case "n" Mins = Mins + Number If Mins >= 60 Or Mins < 0 Then Overflow = Mins \ 60 Call DateAddDoIt("h", Overflow, Days, _ Hrs, Mins, Secs) Mins = Mins Mod 60 If Mins < 0 Then ' borrow an hour Call DateAddDoIt("h", -1, Days, _ Hrs, Mins, Secs) Mins = 60 + Mins End If End If Case "s" Secs = Secs + Number If Secs >= 60 Or Secs < 0 Then Overflow = Secs \ 60 Call DateAddDoIt("n", Overflow, Days, _ Hrs, Mins, Secs) Secs = Secs Mod 60 If Secs < 0 Then ' borrow a minute Call DateAddDoIt("n", -1, Days, _ Hrs, Mins, Secs) Secs = 60 + Secs End If End If End Select End Sub
Because of the recursion, this code is a bit complicated to follow. You can try passing various positive and negative values for altering hours, minutes, and seconds and trace the branching calls yourself. Be sure to watch the parameters that are passed so you don’t lose track of where you are.
The last step in fixing my application is to replace qupdWithDateAdd with qupdWithMyDateAdd, which incorporates MyDateAdd() in place of VBA’s DateAdd() (see Figure 7). After running qupdWithMyDateAdd forward five times and backward (-1 hour) 10 times, there were still no conflicts returned by qryConflictingTimes.
Most of us, at some point while learning to program, have been told not to trust the value of the Double datatype. The precision of the Double datatype is ultimately variable in the wee decimal places, and comparisons between seemingly identical numbers can give unexpected results. This, I believe, is just another example.
All the queries and sample data in this discussion can be found download accompanying this article. I don’t suggest that you give up trusting VBA’s intrinsic functions. You’ll never get any work done. But I do recommend that you pay attention to your data and keep up with your reading for reports of “features” like the one in DateAdd. Imagine if I’d been scheduling trains converging on a switch track.