Working with Expiration Dates

[debbiep]

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

 

[Ken Snell \(MVP\)]

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/AppIssueBadWord.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.aspx?scid=kb;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/codes.asp?ItemID=18#18

 

[debbiep]

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/AppIssueBadWord.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.aspx?scid=kb;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/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

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

 

[debbiep]

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/AppIssueBadWord.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.aspx?scid=kb;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/codes.asp?ItemID=18#18

Ken Snell
<MS ACCESS MVP>

news:[email protected]...

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

 

[debbiep]

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/AppIssueBadWord.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.aspx?scid=kb;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/codes.asp?ItemID=18#18

Ken Snell
<MS ACCESS MVP>

news:[email protected]...

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.

 

[Ken Snell \(MVP\)]

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)

 

[debbiep]

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)

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)

 

[debbiep]

news:[email protected]...

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.

 

[debbiep]

news:[email protected]...

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.

 

[debbiep]

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)

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 -

 

[debbiep]

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

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

 

[Ken Snell \(MVP\)]

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.