generating dates on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday. Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)
 
Week Ending Date: Use the formula
DateAdd("d",7-Weekday([JobDate],2),[JobDate])

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

DateAdd("d",Choose(WeekDay([PEDate],2),7,6,6,6,6,6,5),[PeDate])
 
Thanks John. It works but I don't really understand the formula. Can you
suggest any recommended reading?

John Spencer said:
Week Ending Date: Use the formula
DateAdd("d",7-Weekday([JobDate],2),[JobDate])

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

DateAdd("d",Choose(WeekDay([PEDate],2),7,6,6,6,6,6,5),[PeDate])


Bobbye R said:
I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are
the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the
pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday.
Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)
Click to expand...
Click to expand...
 
The formula is using the VBA function Choose, DateAdd, and Weekday. If you open
a module window, you can type in the formula and highlight each Function name to
get the online help for that function.


Quick synopis:

Weekday returns a number corresponding to the day of the week for a date. I used
the optional argument ",2" to tell the function that Monday was the first day of
the week. So it returns a value between 1 and 7

Choose takes a number argument in the first position and selects the
corresponding value in the following list of values.

DateAdd adds Days(d), months, years, hours, etc to a Date.

Don't have any specific recommendation on books for learning VBA.

Bobbye said:
Thanks John. It works but I don't really understand the formula. Can you
suggest any recommended reading?

John Spencer said:
Week Ending Date: Use the formula
DateAdd("d",7-Weekday([JobDate],2),[JobDate])

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

DateAdd("d",Choose(WeekDay([PEDate],2),7,6,6,6,6,6,5),[PeDate])


Bobbye R said:
I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are
the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the
pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday.
Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)
Click to expand...
Click to expand...
Click to expand...
 
Thanks again for taking the time.
--
Ameteur Access 2000 User
Thanks Bobbye


John Spencer said:
The formula is using the VBA function Choose, DateAdd, and Weekday. If you open
a module window, you can type in the formula and highlight each Function name to
get the online help for that function.


Quick synopis:

Weekday returns a number corresponding to the day of the week for a date. I used
the optional argument ",2" to tell the function that Monday was the first day of
the week. So it returns a value between 1 and 7

Choose takes a number argument in the first position and selects the
corresponding value in the following list of values.

DateAdd adds Days(d), months, years, hours, etc to a Date.

Don't have any specific recommendation on books for learning VBA.

Bobbye said:
Thanks John. It works but I don't really understand the formula. Can you
suggest any recommended reading?

John Spencer said:
Week Ending Date: Use the formula
DateAdd("d",7-Weekday([JobDate],2),[JobDate])

PayDate: Try a formula something like the following. You may need to modify
the 7,6,6,6,6,6,5 string to get the correct date - depending on how well my
coffee fix is working this morning.

DateAdd("d",Choose(WeekDay([PEDate],2),7,6,6,6,6,6,5),[PeDate])


I have a time sheet report that is generated twice a month. The ending
periods are the 10th and 25th of the month [PEDATE] and are pay dates are
the
16th and 1st [PAYDATE]. The week starts Monday and ends Sunday [WKENDING].

PAY DATE:I want this calculated automatically.
[PEDATE]+6 doesn't quite work. If the date falls on a Saturday then the
pay
date will be on a Friday. (For Sunday, the pay date is Monday). Also,
unless the month has 30 days, I don't get the right pay date.

WEEK ENDING DATE:I want this calculated automatically.
I subtotal the time for each week and would like to generate a date on the
report at the end of each week. I would like it to always be Sunday.
Right
now I just use the last [jobdate] of the week, so if a guy works
Monday-Wednesday the week ending date is Wednesday.

Any help would be appreciated.

Access 2000/03 user (ametuer)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top