If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
=DateDiff (Access 2000 / Access 2002)
I'm working in Access 2000 and the =DateDiff does not
work. I have created a form that contain several date fields in which I need to calculate the amount of working days between two date fields, example {Date Received] and [Date Action Taken]; unfortunately, I am not a programmer and I have having a hard time trying to get this to work. Can some one tell me how I can create a field the will calculate the work dates between two date fields in a form. I desperately need help. Someone please rescue me. Sonya |
#2
|
|||
|
|||
=DateDiff (Access 2000 / Access 2002)
Sonya,
The following will calculate the number of business days between two dates, dte1 and dte2. DateDiff("d", dte1, dte2) - _ Choose(Weekday(dte1, vbMonday), 0, 0, 0, 0, 0, 2, 1) - _ Choose(Weekday(dte2, vbMonday), 0, 0, 0, 0, 0, 1, 2) - _ (DateDiff("w", dte1, dte2) * 2) Regards, Graham R Seach Microsoft Access MVP Sydney, Australia Microsoft Access 2003 VBA Programmer's Reference http://www.wiley.com/WileyCDA/WileyT...764559036.html "Sonya" wrote in message ... I'm working in Access 2000 and the =DateDiff does not work. I have created a form that contain several date fields in which I need to calculate the amount of working days between two date fields, example {Date Received] and [Date Action Taken]; unfortunately, I am not a programmer and I have having a hard time trying to get this to work. Can some one tell me how I can create a field the will calculate the work dates between two date fields in a form. I desperately need help. Someone please rescue me. Sonya |
#3
|
|||
|
|||
=DateDiff (Access 2000 / Access 2002)
Sonya,
I posted some code earlier, which I soon after discovered to have a bug. I tried to delete the post, but I'm not sure if it got deleted. The following code will calculate the number of business days between two dates, dte1 and dte2: intWorkDays = DiffWorkDays = DateDiff("d", dte1, dte2) - _ Choose(Weekday(dte1, vbMonday), 0, 0, 0, 0, 0, 2, 1) - _ Choose(Weekday(dte2, vbMonday), 0, 0, 0, 0, 0, 1, 2) - _ IIf(Weekday(dte2, vbMonday) - Weekday(dte1, vbMonday) 0, 2, 0) - _ DateDiff("w", dte1, dte2) * 2 Regards, Graham R Seach Microsoft Access MVP Sydney, Australia Microsoft Access 2003 VBA Programmer's Reference http://www.wiley.com/WileyCDA/WileyT...764559036.html "Sonya" wrote in message ... I'm working in Access 2000 and the =DateDiff does not work. I have created a form that contain several date fields in which I need to calculate the amount of working days between two date fields, example {Date Received] and [Date Action Taken]; unfortunately, I am not a programmer and I have having a hard time trying to get this to work. Can some one tell me how I can create a field the will calculate the work dates between two date fields in a form. I desperately need help. Someone please rescue me. Sonya |
#4
|
|||
|
|||
=DateDiff (Access 2000 / Access 2002)
Hi Graham,
Thanks for the reply; Unfornately, I still need help. Can you tell me where I should be placing the function you gave me. I tried to create a module (for the first time)and I'm having trouble. I also tried to type it in the Control Source in a text box on the form and was unsuccessful. What am I doing wrong? Please help. Sonya -----Original Message----- Sonya, I posted some code earlier, which I soon after discovered to have a bug. I tried to delete the post, but I'm not sure if it got deleted. The following code will calculate the number of business days between two dates, dte1 and dte2: intWorkDays = DiffWorkDays = DateDiff("d", dte1, dte2) - _ Choose(Weekday(dte1, vbMonday), 0, 0, 0, 0, 0, 2, 1) - _ Choose(Weekday(dte2, vbMonday), 0, 0, 0, 0, 0, 1, 2) - _ IIf(Weekday(dte2, vbMonday) - Weekday(dte1, vbMonday) 0, 2, 0) - _ DateDiff("w", dte1, dte2) * 2 Regards, Graham R Seach Microsoft Access MVP Sydney, Australia Microsoft Access 2003 VBA Programmer's Reference http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html "Sonya" wrote in message ... I'm working in Access 2000 and the =DateDiff does not work. I have created a form that contain several date fields in which I need to calculate the amount of working days between two date fields, example {Date Received] and [Date Action Taken]; unfortunately, I am not a programmer and I have having a hard time trying to get this to work. Can some one tell me how I can create a field the will calculate the work dates between two date fields in a form. I desperately need help. Someone please rescue me. Sonya . |
#5
|
|||
|
|||
=DateDiff (Access 2000 / Access 2002)
Sonya,
Create a function called WorkDays: Public Function Diff2WorkDays(dte1 As Date, dte2 As Date) As Integer Diff2WorkDays = DateDiff("d", dte1, dte2) - _ Choose(Weekday(dte1, vbMonday), 0, 0, 0, 0, 0, 2, 1) - _ Choose(Weekday(dte2, vbMonday), 0, 0, 0, 0, 0, 1, 2) - _ IIf(Weekday(dte2, vbMonday) - Weekday(dte1, vbMonday) 0, 2, 0) - _ DateDiff("w", dte1, dte2) * 2 End Function Then in the AfterUpdate event for both [Date Received] and [Date Action Taken], add the following code (where "Me!Difference" is the name of the textbox that will display the number of days between the two dates): Me!Difference = Diff2WorkDays(Nz(Me![Date Received], Date), Nz(Me![Date Action Taken], Date)) Regards, Graham R Seach Microsoft Access MVP Sydney, Australia Microsoft Access 2003 VBA Programmer's Reference http://www.wiley.com/WileyCDA/WileyT...764559036.html "Sonya" wrote in message ... Hi Graham, Thanks for the reply; Unfornately, I still need help. Can you tell me where I should be placing the function you gave me. I tried to create a module (for the first time)and I'm having trouble. I also tried to type it in the Control Source in a text box on the form and was unsuccessful. What am I doing wrong? Please help. Sonya -----Original Message----- Sonya, I posted some code earlier, which I soon after discovered to have a bug. I tried to delete the post, but I'm not sure if it got deleted. The following code will calculate the number of business days between two dates, dte1 and dte2: intWorkDays = DiffWorkDays = DateDiff("d", dte1, dte2) - _ Choose(Weekday(dte1, vbMonday), 0, 0, 0, 0, 0, 2, 1) - _ Choose(Weekday(dte2, vbMonday), 0, 0, 0, 0, 0, 1, 2) - _ IIf(Weekday(dte2, vbMonday) - Weekday(dte1, vbMonday) 0, 2, 0) - _ DateDiff("w", dte1, dte2) * 2 Regards, Graham R Seach Microsoft Access MVP Sydney, Australia Microsoft Access 2003 VBA Programmer's Reference http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html "Sonya" wrote in message ... I'm working in Access 2000 and the =DateDiff does not work. I have created a form that contain several date fields in which I need to calculate the amount of working days between two date fields, example {Date Received] and [Date Action Taken]; unfortunately, I am not a programmer and I have having a hard time trying to get this to work. Can some one tell me how I can create a field the will calculate the work dates between two date fields in a form. I desperately need help. Someone please rescue me. Sonya . |
Thread Tools | |
Display Modes | |
|
|