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

0
debbiep
9/29/2007 5:48:49 PM
access.forms 6864 articles. 1 followers. Follow

11 Replies
690 Views

Similar Articles

[PageSpeed] 10

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

-- 

        Ken Snell
<MS ACCESS MVP>


<debbiep@ywcaofjamestown.com> wrote in message 
news:1191088129.660508.176560@g4g2000hsf.googlegroups.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))
> 


1
Ken
9/29/2007 8:02:46 PM
On Sep 29, 4:02 pm, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> 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/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>
>
> <debb...@ywcaofjamestown.com> wrote in message
>
> news:1191088129.660508.176560@g4g2000hsf.googlegroups.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))

0
debbiep
9/30/2007 2:18:02 AM
On Sep 29, 10:18 pm, "debb...@ywcaofjamestown.com"
<debb...@ywcaofjamestown.com> wrote:
> On Sep 29, 4:02 pm, "Ken Snell \(MVP\)"
>
>
>
>
>
> <kthsneisll...@ncoomcastt.renaetl> 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/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>
>
> > <debb...@ywcaofjamestown.com> wrote in message
>
> >news:1191088129.660508.176560@g4g2000hsf.googlegroups.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

0
debbiep
9/30/2007 2:40:20 AM
On Sep 29, 10:18 pm, "debb...@ywcaofjamestown.com"
<debb...@ywcaofjamestown.com> wrote:
> On Sep 29, 4:02 pm, "Ken Snell \(MVP\)"
>
>
>
>
>
> <kthsneisll...@ncoomcastt.renaetl> 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/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>
>
> > <debb...@ywcaofjamestown.com> wrote in message
>
> >news:1191088129.660508.176560@g4g2000hsf.googlegroups.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.

0
debbiep
9/30/2007 2:40:52 AM
<debbiep@ywcaofjamestown.com> wrote in message 
news:1191120020.372016.73360@r29g2000hsg.googlegroups.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>



0
Ken
9/30/2007 2:50:58 AM
On Sep 29, 10:50 pm, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> <debb...@ywcaofjamestown.com> wrote in message
>
> news:1191120020.372016.73360@r29g2000hsg.googlegroups.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)

0
debbiep
9/30/2007 3:33:57 AM
On Sep 29, 11:33 pm, "debb...@ywcaofjamestown.com"
<debb...@ywcaofjamestown.com> wrote:
> On Sep 29, 10:50 pm, "Ken Snell \(MVP\)"
>
>
>
> <kthsneisll...@ncoomcastt.renaetl> wrote:
> > <debb...@ywcaofjamestown.com> wrote in message
>
> >news:1191120020.372016.73360@r29g2000hsg.googlegroups.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.

0
debbiep
9/30/2007 3:50:40 AM
On Sep 29, 11:33 pm, "debb...@ywcaofjamestown.com"
<debb...@ywcaofjamestown.com> wrote:
> On Sep 29, 10:50 pm, "Ken Snell \(MVP\)"
>
>
>
> <kthsneisll...@ncoomcastt.renaetl> wrote:
> > <debb...@ywcaofjamestown.com> wrote in message
>
> >news:1191120020.372016.73360@r29g2000hsg.googlegroups.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.

0
debbiep
9/30/2007 4:11:53 AM
On Sep 30, 12:11 am, "debb...@ywcaofjamestown.com"
<debb...@ywcaofjamestown.com> wrote:
> On Sep 29, 11:33 pm, "debb...@ywcaofjamestown.com"
>
>
>
>
>
> <debb...@ywcaofjamestown.com> wrote:
> > On Sep 29, 10:50 pm, "Ken Snell \(MVP\)"
>
> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> > > <debb...@ywcaofjamestown.com> wrote in message
>
> > >news:1191120020.372016.73360@r29g2000hsg.googlegroups.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 -


0
debbiep
9/30/2007 4:13:25 AM
On Sep 30, 12:13 am, "debb...@ywcaofjamestown.com"
<debb...@ywcaofjamestown.com> wrote:
> On Sep 30, 12:11 am, "debb...@ywcaofjamestown.com"
>
>
>
> <debb...@ywcaofjamestown.com> wrote:
> > On Sep 29, 11:33 pm, "debb...@ywcaofjamestown.com"
>
> > <debb...@ywcaofjamestown.com> wrote:
> > > On Sep 29, 10:50 pm, "Ken Snell \(MVP\)"
>
> > > <kthsneisll...@ncoomcastt.renaetl> wrote:
> > > > <debb...@ywcaofjamestown.com> wrote in message
>
> > > >news:1191120020.372016.73360@r29g2000hsg.googlegroups.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]))

0
debbiep
9/30/2007 6:36:37 PM
<debbiep@ywcaofjamestown.com> wrote in message 
news:1191177397.834279.157670@19g2000hsx.googlegroups.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>



0
Ken
9/30/2007 7:21:53 PM
Reply:

Similar Artilces:

Hyperlinks to DB dont work now that DB is converted
We have converted all our Access 97 databases to Access 2000- not without a lot of issues. Most of them have now been researched and resolved, some re-coded, etc. We have one issue that we cannot resolve. We used to have hyperlinks on our Intra-net to several Access Databases. These were essentially "shortcuts" to the databases so that the general population could have easy access. Now that we did our Access conversion, the hyperlinks on our intranet do not work. Clicking on the link gives an option to open the file from its current location or download it to a local loc...

Send to Onenote on Windows 7, driver won't work?
I downloaded the driver from David Rasmussen, but every time I open OneNote after printing to XPS I get the message "Unhandled exception has occured in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Object reference not set to an instance of an object." If I click continue it still doesn't work. This was one of my favorite features - PLEASE PLEASE PLEASE find me a way to use OneNote! I lived off of it last year. Rilli wrote: > I downloaded the d...

Date Elimination
I have a worksheet with mainly dates in column A in the format of '25 Aug 2008'. Is it possible with a macro or similar to delete lines beyond a certain date (2 years hence)? Basically, I'm not interested in data more than 2 years old. This would eliminate a lot of data and make for a more viewable worksheet. try this Sub del_date() ActiveCell.Range("A1").Select Do Until ActiveCell.Value =3D "" dt =3D Date - 730 If ActiveCell.Value <=3D dt Then ActiveCell.ClearContents ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop End ...

auto caps don't work
in outlookk 2003, even tho i have set tools, options, spelling to autocorrect the first word of a sentence. it doesn't do it. i have never had this problem before, but now i have a new laptop and can't get this feature to work. -- tom martin Are you using Word as your editor? If so, your settings are there. Otherwise, learn to type. http://www.broderbund.com/jump.jsp?itemID=4713&itemType=CATEGORY&path=1%2C2%2C4713&ysmchn=GGL&ysmcpn=Typing&ysmcrn=sr2br29go633go202pi10ai50&ysmtrm=sr2br29go633go202pi10ai50+mavis+beacon&ysmtac=PPC&ovtac=PPC&SR=s...

Search does not work for one workbook
I have workbook, but search (spotlight) often does not work for it. Drives me crazy. What can this be. I see peoples names in there. Kevs OS 10.6.2 INTEL imac Office 2004 Hi Kevs: It could be the file format the workbook is saved in. What extension does the file-name have? The extension tells the system what kind of data a file contains, which controls which importer Spotlight uses to read the file: if there is no extension you could get strange results. Cheers On 6/01/10 2:47 AM, in article C7689F21.5718F%forums55@verizon.net, "forums55@verizon.net"...

USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...

Pivot table
In the database we use SALES figures are listed per day. I would like to group them per week, per month in a pivot table. I've tried "Grouping", but get an error message. Who can point me in the right direction to get this done? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=499205 Hi Make sure that all of the data in the range are true Excel dates. Text values, or blank cells in...

How does hibernate work
I have a question on the details of how hibernate works. I know it's saving everything in memory to the hiber file. Since power seems to be truly off at hibernation I assumed the flag to tell the PC to resume from the hiber file rather than normal booting must be either saved on disk or flashed to a bit of bios rom space, or maybe in space maintained by the clock battery. But the cure for a laptop that is stuck in a loop of constantly resuming from hibernating is to unplug it and take the battery out for a while so where actually is the flag to signal the system that it's in hi...

working with temporary working files..
I keep getting the message that I cannot save a temp. working file. This work has been save on a cd and transfered back on to my desk top. Help ...

Help with Date Range Formula
I am helping with a spreadsheet and have had little success in trying to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name date of incident Average Monthly Wage John Doe 01/01/2008 4333. Jane Doe 01/01/2003 2888 Sally Doe 01/01/2009 3600 In the example above I would need to have the average monthly wage col...

Non-working work week
Is there any way to make an entire week a non-working work week? As in, a task is going to take 25 days, but the 4th of July occurs in the middle and you want to show that during that week (or 2), no work will occur. Besides creating a new task or extending the task during. Thanks in advance! Hi RVE, Open up the calendar, "Tools", 'Change working time..', then simply select the days you want as non working. Make sure that the calendar you change is the Project calendar and is the basis for all other calendars such as individual resource calendars. Hope thi...

Conditional Format Tracker by Date
I'm building a tracker to make sure paperwork is done on time. Headers: A:Name B:IDNum C:Phone D:StartDate E:Plan F:Paperwork G:30DAuth H:90DAuth I'd like to use conditional formatting to compare the dates and color the cells for easier tracking. The compare TO date is column D. In column E, we have 7 days to write the plan so if there is no date in that column, turn red. When a date is entered turn green if within 7 days but yellow if it's past 7 days. In column F, same as column E but only 3 days for that one. (I can figure that out if I get a w...

Graphing work and remaining work over time
I would like to generate a graph showing total actual work and remaining work for a top level task over time (week), This would enable me to show the projected remaining work through the duration of the project as it decreases from N to 0, and show the actual work completed though the duration of the project as it increases from 0 to N. The canned visual reports don't allow me to do this (that I can see anyway). Maybe I'm missing something. I also can't see how to customize a visual report that will do what I want. Using Microsoft Project 2007 (not the Server edit...

Calculating date/time intervals??
I have multiple sets of date/time value combinations. For each start time (date/time) and finish time (date/time), I need a formula that calculates the number of days, hours, and minutes between the two events. Example: Start 5/14/2010, 11:25 Finish 5/17/2010, 18:30 Thanks for any assistance! John Hi, Assume that starting entry is in cell C7 and finishing entry is in cell C9 =INT(C9)-INT(C7) for days; =(C9-C7)*24*60 for minutes; and =(C9-C7)*24 for days -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jj" <jj@discussions.micr...

GP8 Manufacturing
Hello Everybody. Our Shop works 5 days a week (closed weekends). We have a WC that works 6 days a week (down Sundays only). We have setup both the Shop and WC Calendars to reflect this reality. We would expect that, when scheduling an MO that routes thru this WC, the WC calendar will override the Shop Calendar? However, our tests are not showing this to be true - We use a Backwards Infinite Scheduling Method and it always skips the weekend when calculating the start day of an MO regardless of the WC. Does anyone have any experience working with the WC Calendars? Please share your wis...

Excel 2000
Hello I am new and exhausted from trying to find an answer to m problem. I enter a date into a cell (ex. 020206 which should auto format t 02/02/2006) but when I hit enter I get a weird date that comes u 1/23/7436. This happens with any date I enter. I tried to custom format the cel ##/##/## but that doesn't work, and I tried "text to columns" but ha no success. Any help with detailed solutions would be appreciated. thank -- vt ----------------------------------------------------------------------- vtz's Profile: http://www.excelforum.com/member.php?action=getinfo&us...

outlook2003 send button does not work
send button does not work i have tryed a new email acc. and reinstalling with no luck. now i am useing express and then export to outlook ...

ISO SQL Date Format / ISO Date Formats
ISO date formats come in a number of variations, two such variations are YYYY-MM-DD and YYYYMMDD. The ISO SQL Standard date format is YYYY-MM-DD only which is termed a "date literal". According to the standard (see here: http://savage.net.au/SQL/sql-92.bnf.html#date literal) the "date literal" in order for it to be a "date literal" MUST be prefixed by the keyword DATE, if not then the text is simply nothing more than a "string literal" which has different meaning and subject to regional settings if used on data type conversions ie. st...

Drag and Drop does not work
Hi all. This is funny: When I try to drag and drop a message from "Inbox" to "Backup" I get an error message. When I right click and select "move to folder"- it does work. ANY CLUES? TIA Guy Not a single one. No Outlook version = no clues. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Guy Cohen asked: | Hi all. | | This is funny: | When I try to drag and drop a message from "Inbox" ...

Internet Explorer E-mail links are no longer working in Outlook?
Hi, As soon as I click an e-mail link in Internet Explorer, another Outlook pops up. It used to just open a fresh new e-mail screen with the persons address already in the "TO:" line ? I can't figure out what I did wrong. Thanks, Rich ...

SunTrust Auto-Download Not Working after Banking Upgrade
I have been automatically downloading transactions from SunTrust since I installed MS$ 2007 Deluxe last year. I have done this without having to signup (for a fee of 5.95/month) with SunTrust for MS$ support. AFter the MS Banking Upgrade, I had to re-establish my SunTrust account with MS$. But, now I'm told (by MS$) that I don't have a SunTrust on-line account. Any thoughts? Anyone? I have been having issue as well with Suntrust, even if you get it to work you will find that its basically useless now downloading and duplicating pending transactions repeatedly, this is where I am ...

Spell check not working.
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I reinstalled SL and all my MS office apps and updates. I noticed that spell check does not detect spelling errors. Auto spell check is on and even when I manual invoke spell check it does not detect errors. <br><br>I see a progress bar at the word that is intentionally misspelled and the only dictionary that I see and it is check is a custom dictionary. <br><br>Should I reinstall Word? <br><br>Thanks in advance. No, reinstalling is not a fix on a Mac in the overwhelming m...

Working with option buttons in Word 2002
So, we are trying to create a form in Word 2002 in which we offer a series of radio or "option" buttons for some of our survey questions. However, we are struggling to make the option buttons work through the "Web Tools" toolbar. We can create the list we want to create, but can't save the file so it can be e-mailed and utilized in the correct capacity. Can someone please walk me through the steps of how to create a form in which there are numerous questions, almost all of which utilize the same clickable "option buttons" to choose either a &q...

Working with WORKS
We have some spreadsheets in WORKS 4.5 fpr Windows 95. But Excel 2000 won't open them. It says that the version is incompatible. How can that be if it is an older version of WORKS? Using Windows XP. -- ____________________ Bill DeShawn Please reply to group only Thank you http://my.sterling.net/~bdeshawn Generally you would normally open the file in WORKS and then save it down as a file that Excel can read. .xlr is a native Excel format, or .txt, .csv, dbaseIV are all formats it can read. You can also find a converter here if you don't have WORKS. http://www.rl-software.co...

Date/Serial Number
Hoping you can help me quickly. I exported a report from QuickBooks to Excel. Everything exported correctly and I am manipulating how the report looks. All of a sudden (and I don't know what I did), the Date column (defined as Custom by QB mm/dd/yyyy) is appearing in the display as the serial number. The display line on the top of my screen (underneath my standard toolbar) displays the date correctly. How can I get my workcells to display the date again? I've tried to change the formatting, etc., but it continues to display the serial number in the workcell. THanks, Sally n...