=DateDiff (Access 2000 / Access 2002)

  • Thread starter Thread starter Sonya
  • Start date Start date
S

Sonya

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
 
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/WileyTitle/productCd-0764559036.html
 
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
 
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


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
Click to expand...


.
Click to expand...
 
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/WileyTitle/productCd-0764559036.html


Sonya said:
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


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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top