Working with Expiration Dates

  • Follow


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
Reply debbiep 9/29/2007 5:48:49 PM

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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply 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
Reply Ken 9/30/2007 7:21:53 PM

11 Replies
498 Views

(page loaded in 0.096 seconds)


Reply: