InfoPath Date Math Shuffle

Late Friday I got asked one of those seemingly simple questions whose simple answer is, as it turned out, missing. All my user wanted to do was have the difference between two dates calculate in her Infopath 2003 form. Naturally it’s a piece of cake in 2007… sigh.

But it can be done in 2003 with a fairly short VB script. Problem was I started out trying to write it as a Jscript and kept running into a lack of functionality… So anyway here is how to do the InfoPath 2003 Date Math Shuffle in all it’s glory.

Step One: Set the form to use VB Script in place of Jscript. This can only be done at the start, before you write any code for the form. Select Tools, then form options. This will open the form options dialog box, allowing you to select the Advanced Tab, where you can set the programming language to Visual Basic Script (VBScript as shown above).

To test it I added two date fields and a text field to a blank form.

Once there we can open our editor so we can add the code by grabbing the properties of the second date picker, select the onAfterChange event and click on the Edit button.



This will launch the Microsoft script editor for the proper event.

Of course after we’re done we should go back and add a call to the first date to the second date’s event in case the form’s user changes the first date after the second… heh.

But for now let’s just add the code. Thank goodness that the VB editor seems to have all the functionality that the Jscript lacked. At one point I was even considering a web-service call, but as it turns out that this VB script can handle this in a flash and without any of the nasty delays inherent in a web-service.

So, Step two is to write the code for the event. First we need to leave the premade code alone, it's only there in the event an "If eventObj.IsUnDoReDo" argument is present, and we don't care.

Below is the code, with further descriptions commented in the event you want to cut and paste it in as is...

‘==========
‘ date_from follows the XDocument path to get access to the forms SecondDate Field
‘ date_to is the same for the first date field
‘ dt1Val is a variable where we will load the value of the date_from field
‘dt2Val is a variable where we will load the value of the date_to field
‘daysElapsed is a variable where the difference is calculated
‘==========

Dim date_from
Dim date_to
Dim dt1Val
Dim dt2Val
Dim DaysElapsed

‘=====
‘ Now we set the date_from and date_to so we can read the fields
‘=====

Set date_from = XDocument.DOM.selectSingleNode("/my:myFields/my:SecondDate")
Set date_to = XDocument.DOM.selectSingleNode("/my:myFields/my:FirstDate")

‘======
‘ Then we test the fields to see if we have dates in both fields, if so then we calculate the difference and put
‘ it into the display field on the form, otherwise we make the display field blank
‘======
If (date_from.Text <> "") and (date_to.Text <> "") Then
dt1Val = CDate(date_from.Text)
dt2Val = CDate(date_to.Text)
daysElapsed = DateDiff("d",dt2Val,dt1Val)
XDocument.DOM.selectSingleNode("/my:myFields/my:Difference").Text = daysElapsed
else
XDocument.DOM.selectSingleNode("/my:myFields/my:Difference").Text = ""
End If

All that is left is to call this function when the first date changes by creating it’s event just as you did the second date fields and copying the function call, in this case:

msoxd_my_SecondDate_OnAfterChange(eventObj)

Not too shabby, but having Microsoft add the DateDiff function to InfoPath, as I believe they did in 2007 would have saved my people a lot of trouble.