adding a month to a text box

  • Thread starter Thread starter tonyrulesyall
  • Start date Start date
T

tonyrulesyall

I am using Access XP for my database.

I have a textbox on a report, txtdate1.
This has the value of "=Mar 08"

I would like the next textbox to be the value of txtdate1 plus one
month.

I was working with:
=DateAdd("mmm",1,'[txtdate1]')

but I get #error

What should I be doing differently?
 
Assuming the value of txtdate1 really is a string expression per se and not a
formatted date/time value it depends on whether the 08 represents the year or
the day of the month. If the former try:

=Format(DateAdd("m",1, "1 " & [txtdate1]),"mmm yy")

Concatenating the 1 to the Month/Year is necessary as otherwise a string
such as "Apr 07" will be interpreted as 7 April 2008, i.e. the 07 is
interpreted as the day of the month and the current year assumed. Adding the
1 forces it to be interpreted as the first of the month and the 07 as the
year. As it is then formatted "mmm yy" the day of the month is irrelevant to
the end result of course.

If the latter:

=Format(DateAdd("m",1, [txtdate1]),"mmm dd")

The principle is much the same, but in this case the current year is used as
the default, so there is no need to concatenate it into the string expression.

Ken Sheridan
Stafford, England
 
I am using Access XP for my database.

I have a textbox on a report, txtdate1.
This has the value of "=Mar 08"

I would like the next textbox to be the value of txtdate1 plus one
month.

I was working with:
=DateAdd("mmm",1,'[txtdate1]')

but I get #error

What should I be doing differently?
Click to expand...

1. You should realize that you are not really dealing with dates here. You
are dealing with strings that could possibly be interpreted as date-related
if entered in the proper format. DateAdd (and the rest of the "Date..."
functions) can only be used with actual Date/Time data, or data that can be
implicitly converted into a Date/time value. "Mar 08" does not qualify as an
implicitly convertible string value.

2. You should not be depending on the user to enter your "dates" in the
proper format. There are many ways to accomplish this, but the best way is
to use the calendar control provided by Access. Online help has several
example of using this control so I will not go into it here. The benefit is
that the control supplies an actual date/time value which you can use the
"Date..." functions.
 
Back
Top