Working with Expiration Dates

On Sep 30, 12:13 am, "
wrote:
On Sep 30, 12:11 am, "



wrote:
On Sep 29, 11:33 pm, "

wrote:
On Sep 29, 10:50 pm, "Ken Snell \(MVP\)"

wrote:
wrote in message

oups.com...

What am I doing wrong. Has something to do with missing parentheis.

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kool Kidz", DateSerial(Year(Date()) -
(CLng(Format(Date(),"mmdd"))901), 9, 1))- Hide quoted text -

- Show quoted text -

It works great to a point. Kool Kidz only should default to 9/1/?
only if [Date] is =9/1/07

The expression I provided for when [ClassroomID]="Kool Kidz" should give you
a date of 9/1/07 for a current date (returned by the Date() function, which
gives you today's date) that is between January 1, 2007 and September 1,
2007; and a date of 9/1/08 for a current date of September 2, 2007 through
September 1, 2008.

Perhaps you don't want to test against today's date, but against the value
of the [Date] field in your table? If yes, then replace
Date()
with
[Date]
in the expression in both places where it's found:
DateSerial(Year([Date]) - (CLng(Format([Date],"mmdd"))901), 9, 1)

--

Ken Snell
MS ACCESS MVP

It says that "the expression you entered contains invalid syntax, or
you need to enclose your text data in quotes. Below is the formula I
pasted into ACCESS. Thank you again for you support.

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kool Kidz"),DateSerial(Year([Date]) -
(CLng(Format([Date],"mmdd"))901), 9, 1)- Hide quoted text -

- Show quoted text -

One more question. It works great if the [Date] is 9/2/07 and greater.
Now my situation is if the [Date], for example is 8/31/07, it shows an
expiration date of 9/1/07. If the [Date] is less than 9/2/07, it
needs to be 1 year from [Date]. Is this possible. Thanks again.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

YEAH. I got it. Thank you for all your suggestions. This is the final
formula.

=IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date]))

wrote in message
ups.com...

YEAH. I got it. Thank you for all your suggestions. This is the final
formula.

=IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date]))


Congratulations to you for solving the issue. Good luck.
--

Ken Snell
MS ACCESS MVP