Calculating future dates in Access

[Guest]

I am trying to set the "default value" for a date field in an Access Database. I would like the date to be set two weeks in the future, but the day of the week should always be "Wednesday". Any suggestions?

 

[Ken Snell]

Try this as the Default Value expression (assuming that you always "round
up" to the next week if the current day of the week is Thursday, etc.):

=DateAdd("d", 21 - DatePart("w", Date(), vbThursday), Date())

--
Ken Snell
<MS ACCESS MVP>

I am trying to set the "default value" for a date field in an Access

Database. I would like the date to be set two weeks in the future, but the
day of the week should always be "Wednesday". Any suggestions?

 

[Guest]

I tried out your suggestion but it keeps giving me the word "Error" in form view for the field where I set the default value. When I typed in your formula I typed in the "vbThursday" phrase (without quotation marks). When I went back to it later, Access had placed quotation marks around that phrase.

----- Ken Snell wrote: -----

Try this as the Default Value expression (assuming that you always "round
up" to the next week if the current day of the week is Thursday, etc.):

=DateAdd("d", 21 - DatePart("w", Date(), vbThursday), Date())

--
Ken Snell
<MS ACCESS MVP>

I am trying to set the "default value" for a date field in an Access

Database. I would like the date to be set two weeks in the future, but the
day of the week should always be "Wednesday". Any suggestions?

 

[Ken Snell]

Ah, I keep forgetting that the VBA intrinsic constants don't work in the
properties of a control on a form.

Replace vbThursday with the number 5.

--
Ken Snell
<MS ACCESS MVP>

I tried out your suggestion but it keeps giving me the word "Error" in

form view for the field where I set the default value. When I typed in your
formula I typed in the "vbThursday" phrase (without quotation marks). When
I went back to it later, Access had placed quotation marks around that
phrase.