I WANT THE DB TO SHOW A DATE 10 DAYS FROM THE CURRENT DATE

[pat9103]

I am trying to set up a db for my customers. I post the day that I first had
contact with them, I would like the db to automatically calculate followup
dates 1, 4 and 10 days out, so when I look at the db I can determine which
customers I should call today and whether it is first, second or third fu. I
am using Acess 2003

 

[Tim Johnson]

I am assuming that you are entering the data into a form and not directly
into the table (if this is not the case, you might want to strongly consider
doing so, as it allows a lot more versatility for updating related tables, as
well as in this instance).
If this is the case, in the AfterUpdate event of your original contact
textbox, changing the names withing the braces ({}) to the names of your
fields (while this will also work in an update query, I'd suggest using it in
your form's code.):

me.{OneDay} = dateadd("d",1,me.{OriginalContact})
me.{FourDays} = dateadd("d",4,me.{OriginalContact})
me.{TenDays} = dateadd("d",10,me.{OriginalContact})

 

[Ron2006]

What are you planning to do about weekends?

Tuesday and Wednesday will always result in Saturday and Sunday follow
up for the 4 day follow up.

Wednesday and Thursday will always result in Saturday and Sunday
followup for the 10 day followup.

Ron

 

[Tim Johnson]

Excellent point Ron. If you replace the "d" in the DateAdd function with
"w", the formula will only count weekdays.

 

[Douglas J. Steele]

Not so. While the documentation implies that there's a difference between
"d" and "w", in actual fact there isn't.

 

[Ron2006]

You could add something like this:

For the 4day followup:
dim wkEndDays as integer

me.{FourDays} = dateadd("d",4,me.{OriginalContact})
wkEndDays = DateDiff("ww", me.{OriginalContact},me.{FourDays}, 7) +
_
DateDiff("ww", me.{OriginalContact},me.{FourDays}, 1)
me.{FourDays} = dateadd("d",wkEndDays,me.{OriginalContact})


for the 10 day followup since you are then talking about 10 working
days which is in actuallity 2 weeks (if you are not worried about
holidays):

me.{TenDays} = dateadd("d",14,me.{OriginalContact})


for your knowledge increase,
the Datediff calculation that ends with 7 is counting the number of
Saturdays and the one that ends with 1 is counting the number Sundays
between the two supplied dates.

Ron

 

[Tim Johnson]

Thanks for clearing that up, Doug. I had simply looked it up in the Help for
DateAdd and assumed that it was correct.

It does leave me a bit perplexed, though. Do you know why there is a "w"
and a "d", if they do exactly the same thing? Was this just one of those
things that they were working on and couldn't get right, but they forgot to
remove it from the documentation? Also, thanks for your post & insight, Ron,
this will be very helpful to me in the near future.

 

[Douglas J. Steele]

To be honest, I've heard no rumour about why it's that way.