Working with Expiration Dates

I am trying to set an expiration date in my form. What it does is if
the classroom is Bumble Bears, then add 1 year to the field [Date].
If the classroom is Kool Kidz, then it needs to default to 9/1/? (?
being the year of the next 9/1). This is what I have so far. Can
anybody help. Thank you!

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kool Kidz",9/1/2008))

You need to test the current date and determine if it's later than 1
September so that you can determine the correct year to show. So change your
expression to this:

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


I note that you're using the word Date as the name of a field in your table.
It and many other words are reserved words in ACCESS and should not be used
for control names, field names, etc. Allen Browne (MVP) has a very
comprehensive list of reserved words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/Ap****ueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default...b;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/...p?ItemID=18#18

--

Ken Snell
MS ACCESS MVP


wrote in message
ups.com...
I am trying to set an expiration date in my form. What it does is if
the classroom is Bumble Bears, then add 1 year to the field [Date].
If the classroom is Kool Kidz, then it needs to default to 9/1/? (?
being the year of the next 9/1). This is what I have so far. Can
anybody help. Thank you!

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kool Kidz",9/1/2008))

On Sep 29, 4:02 pm, "Ken Snell \(MVP\)"
wrote:
You need to test the current date and determine if it's later than 1
September so that you can determine the correct year to show. So change your
expression to this:

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

I note that you're using the word Date as the name of a field in your table.
It and many other words are reserved words in ACCESS and should not be used
for control names, field names, etc. Allen Browne (MVP) has a very
comprehensive list of reserved words at his website:

Problem names and reserved words in Accesshttp://www.allenbrowne.com/Ap****ueBadWord.html

See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default...b;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/...p?ItemID=18#18

--

Ken Snell
MS ACCESS MVP

wrote in message

ups.com...



I am trying to set an expiration date in my form. What it does is if
the classroom is Bumble Bears, then add 1 year to the field [Date].
If the classroom is Kool Kidz, then it needs to default to 9/1/? (?
being the year of the next 9/1). This is what I have so far. Can
anybody help. Thank you!

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kool Kidz",9/1/2008))- Hide quoted text -

- Show quoted text -

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))

On Sep 29, 10:18 pm, "
wrote:
On Sep 29, 4:02 pm, "Ken Snell \(MVP\)"





wrote:
You need to test the current date and determine if it's later than 1
September so that you can determine the correct year to show. So change your
expression to this:

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

I note that you're using the word Date as the name of a field in your table.
It and many other words are reserved words in ACCESS and should not be used
for control names, field names, etc. Allen Browne (MVP) has a very
comprehensive list of reserved words at his website:

Problem names and reserved words in Accesshttp://www.allenbrowne.com/Ap****ueBadWord.html

See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default...b;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/...p?ItemID=18#18

--

Ken Snell
MS ACCESS MVP

wrote in message

oups.com...

I am trying to set an expiration date in my form. What it does is if
the classroom is Bumble Bears, then add 1 year to the field [Date].
If the classroom is Kool Kidz, then it needs to default to 9/1/? (?
being the year of the next 9/1). This is what I have so far. Can
anybody help. Thank you!

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kool Kidz",9/1/2008))- Hide quoted text -

- Show quoted text -

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

On Sep 29, 10:18 pm, "
wrote:
On Sep 29, 4:02 pm, "Ken Snell \(MVP\)"





wrote:
You need to test the current date and determine if it's later than 1
September so that you can determine the correct year to show. So change your
expression to this:

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

I note that you're using the word Date as the name of a field in your table.
It and many other words are reserved words in ACCESS and should not be used
for control names, field names, etc. Allen Browne (MVP) has a very
comprehensive list of reserved words at his website:

Problem names and reserved words in Accesshttp://www.allenbrowne.com/Ap****ueBadWord.html

See these Knowledge Base articles for more information about reserved words
and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default...b;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/...p?ItemID=18#18

--

Ken Snell
MS ACCESS MVP

wrote in message

oups.com...

I am trying to set an expiration date in my form. What it does is if
the classroom is Bumble Bears, then add 1 year to the field [Date].
If the classroom is Kool Kidz, then it needs to default to 9/1/? (?
being the year of the next 9/1). This is what I have so far. Can
anybody help. Thank you!

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
([ClassroomID]="Kool Kidz",9/1/2008))- Hide quoted text -

- Show quoted text -

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 -

I did figure out the parenthesis part.

wrote in message
ups.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

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

ups.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)

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 -

ok, great. I got it working. Had to many parenthesis. Thank you so
much for your time.

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 that date. Is this possible. Thanks again.

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 -