 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / General MS InfoPath Questions / May 2006Days between dates
Hi, I am no coding wizard....technical person I am so I need some help with a field calculation. I have seen the useful information on how to calculate the hours between a start time and an end time and was wondering if there is a similar method for calculating the number of days between a start date and an end date? If anyone can shed some light on this for me, I'd be more than grateful. Al... Just adding to this post....I have tried using some of the code listed on here by S.Y.M. Wong A Ton and Sandeep and keep getting the following in the field when it checks the formula: Expected value type: end-of-string Actual value: name Dim -->strStartDate<-- Thanks, Al... > Hi, > [quoted text clipped - 7 lines] > > Al... Unfortunately, you will have to use code for the calculation between dates, since the calculation using rules and filters is too complicated, if not impossible. I've managed to do many tricks with rules, but reached my limit with dates. :) Try taking a look at the date and time solutions on my website: http://enterprise-solutions.swits.net/infopath/solutions.htm#datetime and in particular this one: http://enterprise-solutions.swits.net/infopath/datediff-vbscript-infopath.htm Hope this helps. --- S.Y.M. Wong-A-Ton > Hi, > [quoted text clipped - 7 lines] > > Al... Thanks S.Y.M. Wong-A-Ton. I have implemented the VBScript in the form successfully (i.e. the first section of code), however when I add the other code to the field I receive the error listed below. I am adding this to the 'Default Value' formula. I am assuming this is the 'event handler' you are referring to. If not, where is this section? Thanks and best regards, Alison > Unfortunately, you will have to use code for the calculation between dates, > since the calculation using rules and filters is too complicated, if not [quoted text clipped - 21 lines] > > > > Al... You didn't include the error, but in any case, you mustn't add the code as the formula for the Default Value, but you need to add an event handler for a button or a field. I'm not sure how your form is layout or when the difference should be calculated, but if you have two date pickers and a third field showing the difference, you need to add an OnAfterChange event handler to each date picker and then copy and paste the code in those event handlers. To add an event handler for the date pickers: - Double-click on the field - Click on the [Data Validation...] button - Select the OnAfterChange event - Click on the [Edit...] button which will add the event handler for you and take you to the Microsoft Script Editor You can also take a look at http://enterprise-solutions.swits.net/infopath/adding-time-calculating-differenc es.htm for instructions on how to do this. --- S.Y.M. Wong-A-Ton > Thanks S.Y.M. Wong-A-Ton. > [quoted text clipped - 32 lines] > > > > > > Al... Thank you for responding again. The error was added as a reply to post by me but for some reason is not in this message chain. However, this was when I was placing it in the default value which I know is the wrong place. I have in the form as you suggested - 2 date pickers and an whole number (integer) field to return the number of days. I have now entered the code in the event handlers for both date pickers. The calculating field returns nothing. If I enter a formula in the calculating field I get 'NaN'. I am sorry to be of trouble with this but have no coding background what so ever and a demanding client who says the form MUST be able to do this :( Thanks and regards, Al > You didn't include the error, but in any case, you mustn't add the code as > the formula for the Default Value, but you need to add an event handler for a [quoted text clipped - 53 lines] > > > > > > > > Al... Since more folks have been having problems with the solution posted on my website, I will modify it over the weekend to include a form and controls to show step-by-step how this is done. Check the site out again on Monday. --- S.Y.M. Wong-A-Ton > Thank you for responding again. > [quoted text clipped - 71 lines] > > > > > > > > > > Al... I've updated the solution posted on my website to include an entire step-by-step example of how this can be done. See http://enterprise-solutions.swits.net/infopath/datediff-vbscript-infopath.htm --- S.Y.M. Wong-A-Ton > Thank you for responding again. > [quoted text clipped - 71 lines] > > > > > > > > > > Al... Hi, I have implemented the VBScript (http://enterprise-solutions.swits.net/infopath/datediff-vbscript-infopath.htm) in a New Blank Form, and it's works. But when I use a New Form with a SQL Server Data Base connection, the difference between dates not works, I don't have any results. Someone can help? Thanks in advance, Paulo Pina > I've updated the solution posted on my website to include an entire > step-by-step example of how this can be done. See [quoted text clipped - 77 lines] > > > > > > > > > > > > Al... Since you've bound your form to a database, I'm guessing that you're using the calculation within a repeating section or table? The example on my website uses just two date pickers that are not repeating. If you want to extend the sample to a repeating table, you'll have to retrieve the date pickers and total fields for each row in a repeating group and perform the calculation for each row. --- S.Y.M. Wong-A-Ton > Hi, > [quoted text clipped - 91 lines] > > > > > > > > > > > > > > Al... Hi, Thank you so much for making the changes!!! I have modified the code accordingly and added the extra field and all works great. The user will be extremely happy :) Thank you again and GREAT code.... Cheers, AL > Since you've bound your form to a database, I'm guessing that you're using > the calculation within a repeating section or table? The example on my [quoted text clipped - 100 lines] > > > > > > > > > > > > > > > > Al... Glad I could help. --- S.Y.M. Wong-A-Ton > Hi, > [quoted text clipped - 112 lines] > > > > > > > > > > > > > > > > > > Al... Hoping you can help. I followed your example and successfully added to my form. My form contains a Repeating Section titled 'Action Items' which I am using to track Call Center issues, so during any day we might have a dozen 'Action Items' all of which contain different information. I want to use the Date Difference to track calls that are taking longer than 5 days, but your example only works on the first 'Action Item'. Once a user of the form repeats the section the functionality of your Date Difference no longer works. Is there a way to have the date difference piece work for a repeating section? Thank you in advance for any help. Scott > Glad I could help. > --- [quoted text clipped - 116 lines] > > > > > > > > > > > > > > > > > > > > Al... Please, Mr. S.Y.M. Wong-A-Ton, *how* do I "retrieve the date pickers and total fields for each row in a repeating group and perform the calculation for each row;" I've hit a wall. Since you've bound your form to a database, I'm guessing that you're using the calculation within a repeating section or table? The example on my website uses just two date pickers that are not repeating. If you want to extend the sample to a repeating table, you'll have to retrieve the date pickers and total fields for each row in a repeating group and perform the calculation for each row. --- S.Y.M. Wong-A-Ton |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.