MS Office Forum / General MS InfoPath Questions / July 2005
(Urgent) how to calculate a time diference
|
|
Thread rating:  |
Jhon S. - 19 Jul 2005 22:31 GMT Hi, I'm want to calculate a time diference betwen two text fields which contains hours in the format hh:mm and i want to show the result in another text field please send me some examples. Tank you Jhon
Muhammad Sheraz Siddiqi - 20 Jul 2005 13:22 GMT Hi, I believe you have to use scripting to achieve this task. Write OnAfterChange event handler for both time fields and whenever any of the two time fields' value changes, calculate the different b/w. the two and populate it to the result text field using scripting language.
-Attari-
> Hi, > I'm want to calculate a time diference betwen two text fields which [quoted text clipped - 3 lines] > Tank you > Jhon Scott L. Heim [MSFT] - 20 Jul 2005 14:30 GMT Hi John,
Assuming you are entering times such as: 09:00 = 9 AM and 13:10 = 1:10 PM, here are some sample steps and code:
** NOTE: I would suggest you complete these steps as documented so you can see the process work then you can migrate this to your solution.
- Create a new, blank InfoPath form - Insure this form is set to use VBScript and not JScript - Add 3 text boxes with the following names and formats: - txtStartTime (Time, hh:mm) - txtEndTime (Time, hh:mm) - txtElapsedTime (Text)
- Right-click on txtStartTime and choose Properties - Click the Data Validation button - Select OnAfterChange from the Events list - Click Edit - Add the following code after the comment line: "A field change has occurred..."
If eventObj.Operation = "Insert" Then ' A field change has occurred and the DOM is writable. Write code here to respond to the changes. Dim objEndTime Set objEndTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEndTime") If objEndTime.text <> "" Then CalcTime cdate(objEndTime.text) - cdate(eventObj.Site.text) End If End If
- Complete the same steps as above for the txtEndTime text box and then add the following code in the same manner:
If eventObj.Operation = "Insert" Then ' A field change has occurred and the DOM is writable. Write code here to respond to the changes. Dim objStartTime Set objStartTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtStartTime") If objStartTime.text <> "" Then CalcTime cdate(eventObj.Site.text) - cdate(objStartTime.text) End If End If
- Below all of this code, add the following function:
Function CalcTime(interval) Dim objStartTime Dim objEndTime Dim objElapsedTime 'Get a reference to each field Set objStartTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtStartTime") Set objEndTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEndTime") Set objElapsedTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtElapsedTime") Dim totalhours, totalminutes Dim hours, Minutes
totalhours = Int(CSng(interval * 24)) totalminutes = Int(CSng(interval * 1440)) hours = totalhours Mod 24 Minutes = totalminutes Mod 60 If Len(minutes) = 1 Then Minutes = "0" & Minutes
CalcTime = hours & ":" & Minutes objElapsedTime.text = CalcTime End Function
- Save the code and test by entering, say, 11:30 as a start time and 13:45 as an end time...you should get an elapsed time displayed of 2:15.
I hope this helps!
Best Regards,
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
Jhon S. - 22 Jul 2005 22:19 GMT Thank's Scott, but when i test it ..... I get a syntaxis error just in the function line :
InfoPath cannot open the selected form because of an error in the form's code. The following error occurred:
Syntax error File:script.vbs Line:76 Function CalcTime(interval)
I chek out and i can't find the error, please any help will be much apreciated
Jhon S.
Scott L. Heim [MSFT] - 22 Jul 2005 22:32 GMT Hi Jhon,
Did you setup your form to use VBScript and not JScript?
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
Serdal - 25 Jul 2005 10:39 GMT Hi Scott,
can you give me the sample Code for C# Code, i have the same problem...
not the msdn blog:
http://blogs.msdn.com/infopath/search.aspx?q=calculate+time&p=1
I try this but not working for time
> Hi Jhon, > [quoted text clipped - 4 lines] > > This posting is provided "AS IS" with no warranties, and confers no rights Scott L. Heim [MSFT] - 25 Jul 2005 13:32 GMT Hi Serdal,
The only sample C# code I have is from the BLOG - the other code I have was in VBScript.
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
Jhon S. - 25 Jul 2005 20:36 GMT Hi Scott,
Yes I did, from the tolls/options/desing/VBScript
Can you please explain the method you use to change it from Jscrip to VBscript... I really need to make the time substractions Thanks Ivan
Scott L. Heim [MSFT] - 25 Jul 2005 20:59 GMT Hi,
There are 2 locations by which you can make this change:
- Tools|Options|Design tab - this makes the change for all newly created InfoPath solutions
- Tools|Form Options|Advanced - this makes the change for the current solution
If you had already previously launched the script editor with the scripting option set to, say, JScript then you will not be able to easily change that form to VBScript.
I hope this helps!
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
Jhon S. - 25 Jul 2005 22:28 GMT Hi Scott,
Now Im pretty sure i set it in VBscript but i sitll getting this error:
Syntax error File:script.vbs Line:71 Function CalcTime(interval)
Can you please help me Jhon S.
Scott L. Heim [MSFT] - 25 Jul 2005 22:40 GMT Hi Jhon,
If you would, please post the code that calls this procedure as well as the procedure itself so I can try to see how you have this implemented.
Thanks,
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
Jhon S. - 26 Jul 2005 21:00 GMT Hi Scott, this is the code i use, please take a look al it, Jhon
' This file contains functions for data validation and form-level events. ' Because the functions are referenced in the form definition (.xsf) file, ' it is recommended that you do not modify the name of the function, ' or the name and number of arguments.
' The following line is created by Microsoft Office InfoPath to define the prefixes ' for all the known namespaces in the main XML data file. ' Any modification to the form files made outside of InfoPath ' will not be automatically updated. '<namespacesDefinition> XDocument.DOM.setProperty "SelectionNamespaces", "xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-07-25T20:21:43""" '</namespacesDefinition>
'======= ' 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): /my:myFields/my:txtStartTime ' Note: Information in this comment is not updated after the function handler is created. '======= Sub msoxd_my_txtStartTime_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 ' A field change has occurred and the DOM is writable. Write code here to respond to the changes. Dim objEndTime Set objEndTime =XDocument.DOM.selectSingleNode("//my:myFields/my:txtEndTime") If objEndTime.text <> "" Then CalcTime cdate(objEndTime.text) - cdate(eventObj.Site.text) 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): /my:myFields/my:txtEndTime ' Note: Information in this comment is not updated after the function handler is created. '======= Sub msoxd_my_txtEndTime_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 ' A field change has occurred and the DOM is writable. Write code here to respond to the changes. Dim objStartTime Set objStartTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtStartTime") If objStartTime.text <> "" Then CalcTime cdate(eventObj.Site.text) - cdate(objStartTime.text) End If End If
'Below all of this code, add the following function:
Function CalcTime(interval) Dim objStartTime Dim objEndTime Dim objElapsedTime 'Get a reference to each field Set objStartTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtStartTime") Set objEndTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtEndTime") Set objElapsedTime = XDocument.DOM.selectSingleNode("//my:myFields/my:txtElapsedTime") Dim totalhours, totalminutes Dim hours, Minutes
totalhours = Int(CSng(interval * 24)) totalminutes = Int(CSng(interval * 1440)) hours = totalhours Mod 24 Minutes = totalminutes Mod 60 If Len(minutes) = 1 Then Minutes = "0" & Minutes
CalcTime = hours & ":" & Minutes objElapsedTime.text = CalcTime End Function CalcTime
End Sub
Scott L. Heim [MSFT] - 26 Jul 2005 21:40 GMT Hi Jhon,
I was able to create a successful InfoPath form using the steps I originally provided along with the code you just supplied in your last post; however, it looks like you have the CalcTime function embedded in the "OnAfterChange" event for the txtEndTime field. As I mentioned in my sample steps, the CalcTime function needs be placed after *all* other code on the page. So remove the CalcTime function from the "Sub" procedure and paste it after all other code on the page. In other words, scroll to the bottom of the page (after the End Sub line for the txtEndTime event) and then paste the code.
One last item: in the code you provided, you have the "End Function" line for the CalcTime procedure as: End Function CalcTime - this needs to just be: End Function.
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
Jhon S. - 26 Jul 2005 21:56 GMT Thanks it works ....! Jhon S.
Scott L. Heim [MSFT] - 26 Jul 2005 22:09 GMT Hi Jhon,
That's great to hear! :-)
Take care,
Scott L. Heim Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights
|
|
|