Formula for a date field

[Guest]

Good morning,

I have a text field A for 4-digit year (e.g. 2007). I also have a date
field B. I like to write a formula to add a date into the date field B using
the year from the text field A.

For example, if the text field A is "2007", the formula will calculate into
"12/31/2017" (10 years from text field A) for the date field B.

Thanks.

 

[Douglas J. Steele]

DateAdd("yyyy", [FieldA], [FieldB])

Of course, you'd put that in a query and not store the value in the table.

 

[Guest]

Douglas,

Thanks for the formula and fast response. Both text field A and date field
B are the fields in a table. The purpose of the formula is to input a date
in the Field B after a user inputs a value in the Field A on a form.

After I posted the message, I was playing around with the formula, and below
is the formula that I came up with.

Me.[Field B] = DateSerial(Val([Field A])+7,12,31)

The above formula seems to work. If you have any suggestions on the
formula, please let me know.

Thanks again.

DateAdd("yyyy", [FieldA], [FieldB])

Of course, you'd put that in a query and not store the value in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good morning,

I have a text field A for 4-digit year (e.g. 2007). I also have a date
field B. I like to write a formula to add a date into the date field B
using
the year from the text field A.

For example, if the text field A is "2007", the formula will calculate
into
"12/31/2017" (10 years from text field A) for the date field B.

Thanks.

 

[Douglas J. Steele]

Based on your original question, I would assume you meant

Me.[Field B] = DateSerial(Val([Field A])+10,12,31)

That's a perfectly valid approach.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for the formula and fast response. Both text field A and date
field
B are the fields in a table. The purpose of the formula is to input a
date
in the Field B after a user inputs a value in the Field A on a form.

After I posted the message, I was playing around with the formula, and
below
is the formula that I came up with.

Me.[Field B] = DateSerial(Val([Field A])+7,12,31)

The above formula seems to work. If you have any suggestions on the
formula, please let me know.

Thanks again.

DateAdd("yyyy", [FieldA], [FieldB])

Of course, you'd put that in a query and not store the value in the
table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good morning,

I have a text field A for 4-digit year (e.g. 2007). I also have a date
field B. I like to write a formula to add a date into the date field B
using
the year from the text field A.

For example, if the text field A is "2007", the formula will calculate
into
"12/31/2017" (10 years from text field A) for the date field B.

Thanks.