Expression for future date

[agorman]

Could someone please help.

I have to fields in a form for a membership database that I want to relate to each other, 'date joined' and 'expires on'.

I have been trying to build an expression in the 'expires on' field that calculates the date which would be 12 months from the date entered in the 'date joined' field.

Can anyone please point me in the right direction.

Thanks

Adrian

 

[pbaldy]

You can use the DateAdd() function.

 

[Moniker]

DateAdd would look like this:

DateAdd("yyyy",1,Now())

That would add one year to today's date. Just replace the Now() function with the field containing your date.

~Moniker

 

[pbaldy]

God forbid someone should actually learn how to fish.

 

[Moniker]

LOL Paul...

Sometimes it's just too easy to post the exact response. I'm completely self-trained and have been programming for decades, and I remember all the fishing and trial-and-error stuff I went through. It takes longer, but you're better off for it.

Unfortunately, some people basically want you to write their DB application for them. I try to avoid those. But when it's a simple "What's the command for" type of question, I try to be explicit in the response. You pointed out the DateAdd, and I pointed out the way to use it. We tag-teamed it.

~Moniker

 

[agorman]

Thanks

Thankyou Moniker,

I have been trying to teach myself Access 2003 for a few months now, and have done quite well. I have started to use the expression builder and your examples will help me see how it works, and hopefully be able to build on it from there.

I was aware of DateAdd but not sure what to do with it (even after a number of tries).

Thanks for your help.

Also, thanks Paul for your quick response in the first instance, sorry to seem such a dummy - but I am !

Happy new year

Adrian

 

[agorman]

still unsure

Hi again,

I have tried dateadd and cannot work out how to get what I want (maybe it cannot be done?)

If I type the date 22/4/2006 in field 1 called renewed on, I would like field 2 called expires on to be automatically completed but with 12 months added, i.e. 22/4/2007.

I have managed to get feild 2 to be automatically completed with whatever I put into field 1, but cannot get the year added to the date.

I have 3 books and a training CD which are all great but they do not give examples or explination of this particular task.

Please can you help me - I have been fishing for days, and caught nothing !

Regards again

Adrian

 

[johncallaghan]

in the textbox that you enter the field 1 date use the text1_AfterUpdate event to say
text2 = dateadd("yyyy",1,text1)
or
text2 = dateadd("dddd",364,text1)
Good luck
John

 

[agorman]

Hi John,

Thanks for that - I still couldn't work it out, but finally I put
=DateAdd("yyyy",1,[renewedOn]) in the control sauce of properties of the expiresOn field, and it works fine.

Is this a stable way of doing it, or have i acheived my require result but will have problems later on?

Best wishes and Happy New Year

Adrian

 

[johncallaghan]

this works until you edit the data in 'RenewedON' and then it is not valid until you re-load this page.

If you use the event 'AfterUpdate' for the renewedON textbox, and then enter the code, it will update on screen as soon as you have edited the renewedOn textbox.

Cheers

John