Adding 1 year to a date field and autofilling to another field

[dcecil]

I have a field called "Requested Date". I would like it to automatically fill in my "Renewall Date" field with the data from "Requested Date", plus 1 year.



Any help would be greatly appreciated and please instruct me where to put the calculation that is provided.

Thanks,
Dave

[TheShabz]

UPDATE mytable SET mytable.renewall = DateAdd("yyyy",1,[mytable].[requested]);
change "mytable" "renewall" and "requested" to match your table and field names.

If this solves your issue, please mark thread solved.

[ajetrumpet]

If this solves your issue, please mark thread solved.

boy this really shows committment. your posts read like it almost seems to be a burden for you to be here. if so, why not leave?

[TheShabz]

They aren't intended to be that way at all. I'm sorry they sound that way. I only put that at the end of posts where I believe I have fully answered the question or provided sufficient information to help the original poster. I put it because many people forget or do not know about the "solved" feature. I believe it is important to mark it solved so that if someone searches this forum before posting a question (which is what everyone should be doing), they will see a relevant thread. If that thread is marked "solved," there is a high probability that that thread contains the solution of the searcher.

In any case, I'm not one to put people down (unless I feel its called for), nor will I sugar coat anything. I could spend 2 paragraphs sugar coating an SQL string or making a "friendly" reminder. I just would rather not do so.

Hope this cleared things up a bit. I love being in this forum. It's been helpful to me and I'm glad to give back to the community what limited knowledge I do have.

[dcecil]

Thanks, but I do not know where to put "SET mytable.renewall = DateAdd("yyyy",1,[mytable].[requested])". Do I put it in the Default Value field? Can you please be a little more specific, as I'm not a very Access-savvy person.

Thanks,
Dave

[Rawb]

TheShabz's reply is a SQL statement to fill in the field for any records already in your table. To implement his suggestion just make a Query, switch to SQL View, and paste his SQL statement into it.

As for changing your Form to automatically fill this in for new records, place the following code in the Control Source field of your Renewal Date's textbox:

=DateAdd("yyyy",1,[mytable].[requested])

Of course, you'll need to change [mytable].[requested] to the actual table and field names you're using.

P.S.
Credit goes to TheShabz whose SQL statement I shamelessly stole and modified.

[dcecil]

I guess I didn't explain it well enough. I do not have any records yet. I just need to know if I can do it, so I'm just entering test data.

I have the Renewal Date field on a form. Is this where I place the
=DateAdd("yyyy",1,[mytable].[requested]) string?

[Rawb]

That's the one!

[dcecil]

Sorry for being difficult Rawb, I guess I'm just not that bright today. This is what I entered in the Control Source for my Renewal Date Field on my form:

=DateAdd("yyyy",1,[User Forms].[Request Date]) and nothing happens, other than a "#Name?" appears in the Renewal Date field.

User Forms is my table name and Request Date is the date that I want to add 1 year to.

What am I doing wrong?

[dcecil]

I figured it out on my own!!! I took off the bracket at the end of the tablename and at the beginning of the field name.

=DateAdd("yyyy",1,[User Forms.Request Date])

Thanks everybody!!!

[TheShabz]

glad it worked out for you. Please mark this thread solved so that others can come to it for answers as well. Its under the "thread tools" up top.

Rawb, thanks for the backup there.