If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Working with Expiration Dates
On Sep 30, 12:13 am, "
wrote: On Sep 30, 12:11 am, " wrote: On Sep 29, 11:33 pm, " wrote: On Sep 29, 10:50 pm, "Ken Snell \(MVP\)" wrote: wrote in message oups.com... What am I doing wrong. Has something to do with missing parentheis. =Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]), ([ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]), ([ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]), ([ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]), ([ClassroomID]="Kool Kidz", DateSerial(Year(Date()) - (CLng(Format(Date(),"mmdd"))901), 9, 1))- Hide quoted text - - Show quoted text - It works great to a point. Kool Kidz only should default to 9/1/? only if [Date] is =9/1/07 The expression I provided for when [ClassroomID]="Kool Kidz" should give you a date of 9/1/07 for a current date (returned by the Date() function, which gives you today's date) that is between January 1, 2007 and September 1, 2007; and a date of 9/1/08 for a current date of September 2, 2007 through September 1, 2008. Perhaps you don't want to test against today's date, but against the value of the [Date] field in your table? If yes, then replace Date() with [Date] in the expression in both places where it's found: DateSerial(Year([Date]) - (CLng(Format([Date],"mmdd"))901), 9, 1) -- Ken Snell MS ACCESS MVP It says that "the expression you entered contains invalid syntax, or you need to enclose your text data in quotes. Below is the formula I pasted into ACCESS. Thank you again for you support. =Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]), [ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]), [ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]), [ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]), [ClassroomID]="Kool Kidz"),DateSerial(Year([Date]) - (CLng(Format([Date],"mmdd"))901), 9, 1)- Hide quoted text - - Show quoted text - One more question. It works great if the [Date] is 9/2/07 and greater. Now my situation is if the [Date], for example is 8/31/07, it shows an expiration date of 9/1/07. If the [Date] is less than 9/2/07, it needs to be 1 year from [Date]. Is this possible. Thanks again.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - YEAH. I got it. Thank you for all your suggestions. This is the final formula. =IIf([ClassroomID]="Kool Kidz" And (Format([Date],"mmdd")901),DateSerial(Year([Date])- (CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date])) |
#12
|
|||
|
|||
Working with Expiration Dates
wrote in message
ups.com... YEAH. I got it. Thank you for all your suggestions. This is the final formula. =IIf([ClassroomID]="Kool Kidz" And (Format([Date],"mmdd")901),DateSerial(Year([Date])- (CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date])) Congratulations to you for solving the issue. Good luck. -- Ken Snell MS ACCESS MVP |
|
Thread Tools | |
Display Modes | |
|
|