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
|
|||
|
|||
Add 7 days to last record date?
Hello,
I am still a novice at database design so maybe this will be easy. I would like the default value for my date column to continually increase by seven days. It is a schedule and the date value needs to be every Monday. |
#2
|
|||
|
|||
Add 7 days to last record date?
Which Monday. The most recent Monday, or the Next Monday?
Set the default value to something like: =Dateadd("d", -1 * weekday(date(), vbMonday) + 1, date()) This will give you today (if today is a monday), or the most recent Monday already completed. You can either set this as the default value of the field (in the database table), or in the control that is bound to that field on a form. To get the upcoming Monday, use: =Dateadd("d", 7- weekday(date(), vbMonday) + 1, date()) -- HTH Dale email address is invalid Please reply to newsgroup only. "Zach" wrote: Hello, I am still a novice at database design so maybe this will be easy. I would like the default value for my date column to continually increase by seven days. It is a schedule and the date value needs to be every Monday. |
#3
|
|||
|
|||
Add 7 days to last record date?
My previous reply does not seem to have posted properly.
To get the most recent Monday, try: =Dateadd("d", - weekday(date(), vbTuesday), date()) To get the next Monday, try: Dateadd("d", 7- weekday(date(), vbTuesday), date()) The Weekday( ) function returns the numeric value of the day of the week, and accepts an optional second parameter which allows you to set the start day of the week. In this case, we are using Monday as the start day of the week. So, Weekday(Date(), vbTuesday) will return 1 on 23 Dec, and subtracting 1 from 23 gives you 22. If you are putting this in the default value of the field, in a table, you will probably have to replace vbTuesday with the value 3 -- HTH Dale email address is invalid Please reply to newsgroup only. "Zach" wrote: Hello, I am still a novice at database design so maybe this will be easy. I would like the default value for my date column to continually increase by seven days. It is a schedule and the date value needs to be every Monday. |
#4
|
|||
|
|||
Add 7 days to last record date?
Hi Zach,
Try: =Date()+7 Regards, Sam |
#5
|
|||
|
|||
Add 7 days to last record date?
I only make the schedule once every six weeks, so I need it to look at the
last record and add 7 days to it. It just gets tedious entering all those dates. "Dale Fye" wrote: My previous reply does not seem to have posted properly. To get the most recent Monday, try: =Dateadd("d", - weekday(date(), vbTuesday), date()) To get the next Monday, try: Dateadd("d", 7- weekday(date(), vbTuesday), date()) The Weekday( ) function returns the numeric value of the day of the week, and accepts an optional second parameter which allows you to set the start day of the week. In this case, we are using Monday as the start day of the week. So, Weekday(Date(), vbTuesday) will return 1 on 23 Dec, and subtracting 1 from 23 gives you 22. If you are putting this in the default value of the field, in a table, you will probably have to replace vbTuesday with the value 3 -- HTH Dale email address is invalid Please reply to newsgroup only. "Zach" wrote: Hello, I am still a novice at database design so maybe this will be easy. I would like the default value for my date column to continually increase by seven days. It is a schedule and the date value needs to be every Monday. |
Thread Tools | |
Display Modes | |
|
|