Calculatiing date for Conditional Formatting

[mdsmitty]

I want to set a condition on a date field based on the difference between 2
date fields. I have 2 date fields date1 and date2:
date1 is today's date
date2 is a passed date (ie 6 months ago)

If date1 (today) - date2 (passed date) > 365 then I want date2 shaded in red.

Is this a possible using Conditional Formatting?

Thanks
Smitty

 

[Scott L. Heim [MSFT]]

Hi Smitty,

Yes but not in the manner in which you are trying to do this. The only way
to perform calculations on dates is through script. So what you would need
to do is have custom script that compares the dates. If the comparison is
true (i.e. date1 - date2 > 365) then you could set another non-related
field (i.e. a check box field) to True. Finally, you would use conditional
formatting on your Date2 field to look at the value of the check box - if
it's checked, then "shade me red!"

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[mdsmitty]

Thanks Scott, will any of the script examples found here work? I am not much
of a programmer so if you have some sample script, that will help me in the
right direction.

Thanks again
Smitty

 

[Scott L. Heim [MSFT]]

Hi Smitty,

Here are sample steps and code:

- Create a new, blank InfoPath form
- Add 2 Date Picker controls (field1 and field2) and add a check box
control (field3)
- From the Tools menu, choose Form Options, select the Advanced Tab, make
sure the Form Code Language is set to VBScript and click OK
- Right-click on field1, choose Properties, click the Data Validation
button, from the Events box choose OnAfterChange and click Edit - you
should see the following:

Sub msoxd_my_field1_OnAfterChange(eventObj)

' Write code here to restore the global state.

If eventObj.IsUndoRedo Then
' An undo or redo operation has occurred and the DOM is read-only.
Exit Sub
End If

' A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

End Sub

- Just before the End Sub line, add this code:

If eventObj.Operation = "Insert" Then
Dim objDate2
Dim objCheckBox

Set objDate2 = XDocument.DOM.selectSingleNode("//my:myFields/my:field2")
Set objCheckBox = XDocument.DOM.selectSingleNode("//my:myFields/my:field3")

If objDate2.text <> "" Then
If CalcDays(eventObj.Site.text, objDate2.text) > 365 Then
objCheckBox.text = "true"
Else
objCheckBox.text = "false"
End If
End If
End If

- Switch back to the InfoPath designer
- Right-click on field2, choose Properties, click the Data Validation
button, from the Events box choose OnAfterChange and click Edit - again,
just before the End Sub line, add this code:


If eventObj.Operation = "Insert" Then
Dim objDate1
Dim objCheckBox

Set objDate1 = XDocument.DOM.selectSingleNode("//my:myFields/my:field1")
Set objCheckBox = XDocument.DOM.selectSingleNode("//my:myFields/my:field3")

If objDate1.text <> "" Then
If CalcDays(objDate1.text, eventObj.Site.text) > 365 Then
objCheckBox.text = "true"
Else
objCheckBox.text = "false"
End If
End If
End If

- Now, scroll all the way down past all code, make sure your mouse cursor
is blinking in the "white" area and then add this code:

Function CalcDays(date1, date2)
CalcDays = DateDiff("d", date1, date2)
End Function

- Save and close the Script Editor
- Right-click on field2, choose Properties, select the Display tab and
click the Conditional Formatting button
- From the first box, choose: field3
- From the second box, choose: is equal to
- From the third box, choose: TRUE
- From the Shading box, choose Red
- Click OK until you are back to your form
- Preview and enter: 9/1/2005 in field1 and 9/2/2006 in field2 - does
field2 change to red? If so, modify field2 to: 9/1/2006 - it should now go
back to white!

If this works as you need, you can actually delete the check box from the
form (not the Data Source - just the form) as it is not necessary to have
this on the form.

Let me know if this works for you!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[mdsmitty]

Scott, here is the code in my form. I get the following error message.

Object Required: 'objCheckBox'
File:script.vbs
Line: 104

I have an * next to line 104...I get the message at line 69 when I bypass
the error message in the Preview. I presume one fix fixes all.

'=======
' The following function handler is created by Microsoft Office InfoPath.
' Do not modify the name of the function, or the name and number of arguments.
' This function is associated with the following field or group (XPath):
/dfs:myFields/dfs:dataFields/d:Account/d:WP_WSubj/@LastMammo_Date
' Note: Information in this comment is not updated after the function
handler is created.
'=======
Sub msoxd__WP_WSubj_LastMammo_Date_attr_OnAfterChange(eventObj)

' Write code here to restore the global state.

If eventObj.IsUndoRedo Then
' An undo or redo operation has occurred and the DOM is read-only.
Exit Sub
End If

' A field change has occurred and the DOM is writable. Write code here to
respond to the changes.
If eventObj.Operation = "Insert" Then
Dim objDate2
Dim objCheckBox

Set objDate2 =
XDocument.DOM.selectSingleNode("//dfs:dataFields/d:Account/d:WP_WSubj/@Date")
Set objCheckBox = XDocument.DOM.selectSingleNode("//my:myFields/my:AllSysNml")

If objDate2.text <> "" Then
If CalcDays(eventObj.Site.text, objDate2.text) > 365 Then
* objCheckBox.text = "True"
Else
* objCheckBox.text = "False"
End If
End If
End If
End Sub

'=======
' The following function handler is created by Microsoft Office InfoPath.
' Do not modify the name of the function, or the name and number of arguments.
' This function is associated with the following field or group (XPath):
/dfs:myFields/dfs:dataFields/d:Account/d:WP_WSubj/@Date
' Note: Information in this comment is not updated after the function
handler is created.
'=======
Sub msoxd__WP_WSubj_Date_attr_OnAfterChange(eventObj)

' Write code here to restore the global state.

If eventObj.IsUndoRedo Then
' An undo or redo operation has occurred and the DOM is read-only.
Exit Sub
End If

' A field change has occurred and the DOM is writable. Write code here to
respond to the changes.
If eventObj.Operation = "Insert" Then
Dim objDate1
Dim objCheckBox

Set objDate1 =
XDocument.DOM.selectSingleNode("//dfs:dataFields/d:Account/d:WP_WSubj/@Date")
Set objCheckBox = XDocument.DOM.selectSingleNode("//my:myFields/my:AllSysNml")

If objDate1.text <> "" Then
If CalcDays(objDate1.text, eventObj.Site.text) > 365 Then
* objCheckBox.text = "True"
Else
* objCheckBox.text = "False"
End If
End If
End If
End Sub

 

[Scott L. Heim [MSFT]]

It appears your form is bound to a database - correct? If so, how did you
add the check box control? Is this control part of the database fields or
did you right-click on "myFields" in the Data Source and add this node?

Also, even though this is VBScript the XPath information is case sensitive
- so is that node spelled correctly?

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[mdsmitty]

Hey Scott,
Thanks again...Yes it is connected to a DB and yes, I added the check box to
"myFields". I created the XPath by cheating which may be the problem. I
went into the Formula selection from a Conditional Formatting DDL and
inserted the Date fields (1 at a time) then clicked the XPath check box
copied it and pasted into my VBScript. As I said, I don't program at all but
have found some ways to cheat the system so to speak...some work and some
don't. Anyway, I believe sense I copied it that the "case" should be OK. Is
there anywhere that the full XPath can be found?

Thanks
Smitty

 

[Scott L. Heim [MSFT]]

Hi Smitty,

I typically use an Expression Box control but I think this gets you to the
same location. (Select that control, click the button, select your field
and the enable the Display XPath option.)

If it is basically the same, you could try the following:

Set objCheckBox = XDocument.DOM.selectSingleNode("//my:AllSysNml")

or

Set objCheckBox = XDocument.DOM.selectSingleNode("my:AllSysNml")

Just to see if either has an impact...

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[mdsmitty]

BINGO!!!

Thanks Scott, that was it. I changed the ("//my:myFields/my:AllSysNml") to
("my:AllSysNml") and it works perfectly.

Thanks again, you've been a great help. Enjoy the holiday weekend.

Thanks
Smitty

 

[Scott L. Heim [MSFT]]

Hi Smitty,

That's great! Thank you for the update and you enjoy the long weekend as
well!

Take care,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.