Calculating Date Problem

[Liz_I3]

I have 2 text boxes. Text1 and Text2
both are formated as short date.
In text1 a date is keyed in i.e. 12/02/04 (Dec 2/04)
I want text2 to show the date 6months from the date entered in Text1.

In text2 I am entering the Date worksheet funtion.

=Date(Year(text1),Month(text1)+6,Day(text1) I also tried
=Date(Year([text1]),Month([text1])+6,Day([text1])

In both cases I receive the error message "function contains the wrong number of arguments"

Is does this fuction not work in access ( It works for me in excel)? If this is the case, how can I get this calculation to work in access?

Thanks
Liz

 

[Corticus]

Access doesn't recognize the date() function like your thinking, that's a worksheet function only. The reason you get the wrong number of arguments is because Access will return the current date when the date() is used with no arguments (like now()).

Try:
IIf(Month([date])>6,CDate(Month([date])-6 & "/" & Day([date]) & "/" & Year([date])+1),CDate(Month([date])+6 & "/" & Day([date]) & "/" & Year([date])))

Replace [date] with your control name, it looks like text1.

 

[Andrew Fergus]

Hi Liz,

An alternative method to that proposed by Corticus is to replace your formula with this :

=DateAdd("m", 6, [text1])

HTH, Andrew.

 

[Corticus]

Ah yes, DateAdd() is better.

 

[Liz_I3]

Thank you both very much.

the =DateAdd("m", 6, [text1])

worked Beautifully
Liz