434,921 Members | 1,433 Online
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Looking for Expression using date conditions.

 P: n/a Here's my dilemma. I am putting together a trend report using PivotCharts and so the query that I am trying to construct basically would look at the "Date_Entered" for a record and if the "day" portion of the Date is <= 15 (ie 1/1/2006 - 1/15/2006) it will populate a temp column with the actual month and year. However if the "day" portion of the Date is > 15 (ie 1/16/2006 - 1/31/2006) it will populate a temp column with the following month (actual month + 1) and year. This the is the current expression as I have it constructed: MonthOpened: IIf(Month([date_entered])>9 And Day([date_entered])<=15,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) Or IIf(Month([date_entered])>9 And Day([date_entered])>15,Year([date_entered]) & Month([date_entered]) + 1,Year([date_entered]) & '0' & Month([date_entered]) + 1) Anhy guidance that I can get from any of you Access MVPs woud be extremely welcome. Thanks Marc May 25 '06 #1
17 Replies

 P: n/a I am not an MVP and you should not blindly rely on MVPs! I will assume from your problem statement that the "day" portion of the Date in the temp column will be the same as the "day" portion of Date_Entered. MonthOpened:IIF(Day([Date_Entered]) <= 15, [Date_Entered], DateAdd("m",1,(Date_Entered]) You can copy and paste this expression into your query to test if it gives you what you want. If it does not, explain why and I will give you another expression based on your reason why it does not. -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications Over 1175 users have come to me from the newsgroups requesting help re******@pcdatasheet.com "Mr.Kane" wrote in message news:11*********************@u72g2000cwu.googlegro ups.com... Here's my dilemma. I am putting together a trend report using PivotCharts and so the query that I am trying to construct basically would look at the "Date_Entered" for a record and if the "day" portion of the Date is <= 15 (ie 1/1/2006 - 1/15/2006) it will populate a temp column with the actual month and year. However if the "day" portion of the Date is > 15 (ie 1/16/2006 - 1/31/2006) it will populate a temp column with the following month (actual month + 1) and year. This the is the current expression as I have it constructed: MonthOpened: IIf(Month([date_entered])>9 And Day([date_entered])<=15,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) Or IIf(Month([date_entered])>9 And Day([date_entered])>15,Year([date_entered]) & Month([date_entered]) + 1,Year([date_entered]) & '0' & Month([date_entered]) + 1) Anhy guidance that I can get from any of you Access MVPs woud be extremely welcome. Thanks Marc May 26 '06 #2

 P: n/a "PC Datasheet" wrote in message news:xC**************@newsread3.news.pas.earthlink .net...I am not an MVP and with the way you behave and the amount of wrong information you spout, it is very unlikely you will be. and you should not blindly rely on MVPs! They have far more credibility than you will ever attain. The MVP award is an annual award from Microsoft that is given to individuals for their product knowledge and FREE community support. Most of the current Access MVPs have recieved an annual MVP award since they were first awarded. John... Visio MVP May 26 '06 #4

 P: n/a Oh, so you mean *advertising* in your posts will pretty much cause you never to be given an MVP award????!!!! (tongue in cheek, of course!) May 26 '06 #5

 P: n/a The following expression did work, MonthOpened:IIF(Day([Date_Entered]) <= 15, [Date_Entered], DateAdd("m",1,(Date_Entered]) however I need the output to be in (YYYYMM) format, that's why my expression: MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) was constructed that way. How can I add the condition (IIF Day(date_entered)>15, then Year([date_entered]) & Month([date_entered])+1 to my expression? Thanks again and I apologize for being picky... May 26 '06 #6

 P: n/a "Mr.Kane" wrote in news:11*********************@u72g2000cwu.googlegro ups.com: Here's my dilemma. I am putting together a trend report using PivotCharts and so the query that I am trying to construct basically would look at the "Date_Entered" for a record and if the "day" portion of the Date is <= 15 (ie 1/1/2006 - 1/15/2006) it will populate a temp column with the actual month and year. However if the "day" portion of the Date is > 15 (ie 1/16/2006 - 1/31/2006) it will populate a temp column with the following month (actual month + 1) and year. This the is the current expression as I have it constructed: MonthOpened: IIf(Month([date_entered])>9 And Day([date_entered])<=15,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) Or IIf(Month([date_entered])>9 And Day([date_entered])>15,Year([date_entered]) & Month([date_entered]) + 1,Year([date_entered]) & '0' & Month([date_entered]) + 1) Anhy guidance that I can get from any of you Access MVPs woud be extremely welcome. Thanks Marc First, if you put the year first, you will be able to sort properly. You code fails to correctly handle dates after Dec 15, which should fall into the next year. Working with numbers instead of strings is an advantage in a case like this, you don't have to worry about zeroes for months 1-9. MonthOpened: Year([date_entered]*100+ IIF(month([date_entered])=12 and day([date_entered])>15,1,0) +Month([date_entered])+iif(day([date_entered])>15,1,0) 200606 for today. store as a long integer or convert to a string -- Bob Quintal PA is y I've altered my email address. May 26 '06 #7

 P: n/a "Mr.Kane" wrote in news:11*********************@38g2000cwa.googlegrou ps.com: The following expression did work, MonthOpened:IIF(Day([Date_Entered]) <= 15, [Date_Entered], DateAdd("m",1,(Date_Entered]) however I need the output to be in (YYYYMM) format, that's why my expression: MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) was constructed that way. How can I add the condition (IIF Day(date_entered)>15, then Year([date_entered]) & Month([date_entered])+1 to my expression? Thanks again and I apologize for being picky... Don't apologise to PCD, you are entitled to a correct response. The man hasn't furnished a correct, relevant answer in the several years I'v been reading this group. see my separate response to your question for a solution. -- Bob Quintal PA is y I've altered my email address. May 26 '06 #8

 P: n/a Bob Quintal wrote in news:Xn**********************@207.35.177.135: MonthOpened: Year([date_entered]*100+ IIF(month([date_entered])=12 and day([date_entered])>15,1,0) +Month([date_entered])+iif(day([date_entered])>15,1,0) 200606 for today. store as a long integer or convert to a string me bad! I forgot to add some parentheses and to fix month 13. Remind me to debug first, post after. MonthOpened: Year([date_entered])*100+ IIf(Month([date_entered])=12 And Day([date_entered])>15,100,0) + (Month([date_entered]) + IIf(Day([date_entered])>15,1,0)) Mod 12 -- Bob Quintal PA is y I've altered my email address. May 26 '06 #9

 P: n/a Change to this: MonthOpened:Format((IIF(Day([Date_Entered]) <= 15, [Date_Entered], DateAdd("m",1,(Date_Entered])),"yyyymm") -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications Over 1175 users have come to me from the newsgroups requesting help re******@pcdatasheet.com "Mr.Kane" wrote in message news:11*********************@38g2000cwa.googlegrou ps.com... The following expression did work, MonthOpened:IIF(Day([Date_Entered]) <= 15, [Date_Entered], DateAdd("m",1,(Date_Entered]) however I need the output to be in (YYYYMM) format, that's why my expression: MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) was constructed that way. How can I add the condition (IIF Day(date_entered)>15, then Year([date_entered]) & Month([date_entered])+1 to my expression? Thanks again and I apologize for being picky... May 26 '06 #10

 P: n/a Did the new expression I gave you give you what you wanted? -- PC Datasheet Your Resource For Help With Access, Excel And Word Applications Over 1175 users have come to me from the newsgroups requesting help re******@pcdatasheet.com "Mr.Kane" wrote in message news:11*********************@38g2000cwa.googlegrou ps.com... The following expression did work, MonthOpened:IIF(Day([Date_Entered]) <= 15, [Date_Entered], DateAdd("m",1,(Date_Entered]) however I need the output to be in (YYYYMM) format, that's why my expression: MonthOpened: IIf(Month([date_entered])>9,Year([date_entered]) & Month([date_entered]),Year([date_entered]) & '0' & Month([date_entered])) was constructed that way. How can I add the condition (IIF Day(date_entered)>15, then Year([date_entered]) & Month([date_entered])+1 to my expression? Thanks again and I apologize for being picky... May 27 '06 #11

 P: n/a Bob, Thank you for the mod to Steve's original expression. May 30 '06 #12

 P: n/a "Mr.Kane" wrote in message news:11*********************@j73g2000cwa.googlegro ups.com... Bob, Thank you for the mod to Steve's original expression. Praise and put-down in the same phrase, cool. Keith. May 31 '06 #13

 P: n/a Keith Wilby wrote: "Mr.Kane" wrote in message news:11*********************@j73g2000cwa.googlegro ups.com... Bob, Thank you for the mod to Steve's original expression. Praise and put-down in the same phrase, cool. Keith. I wasn't trying to insult anyone I just used Bob's modded expression and was thanking him for his time. I appreciate Steve taking the time to answer my question as well. I'm not interested in jumping into the fray here. May 31 '06 #14

 P: n/a Everything looks good, however it seems that the records from Dec 2004 with dates less or equal to the 15th are defaulting to "YYYY00" ie record date entered 12/5/2004 = "200400" record date entered 12/11/2005 = "200500" record date entered 12/2/2004 = "200400" This the the active expression being used: MonthOpened: Year([date_entered])*100+IIf(Month([date_entered])=12 And Day([date_entered])>15,100,0)+(Month([date_entered])+IIf(Day([date_entered])>15,1,0)) Mod 12 any additonal help would be appreciated (I'll try and tweak the expression and see if I can resolve the conflict as well) May 31 '06 #15

 P: n/a "Mr.Kane" wrote in news:11**********************@i40g2000cwc.googlegr oups.com: Everything looks good, however it seems that the records from Dec 2004 with dates less or equal to the 15th are defaulting to "YYYY00" ie record date entered 12/5/2004 = "200400" record date entered 12/11/2005 = "200500" record date entered 12/2/2004 = "200400" any additonal help would be appreciated (I'll try and tweak the expression and see if I can resolve the conflict as well) MonthOpened: Year([calldate])*100+IIf(Month([calldate])=12 And Day([calldate])>15,100,0)+(Month([calldate])+IIf(Day ([calldate])>15,1,0)) Mod 12+IIf(Month([calldate])=12 And Day ([calldate])<=15,12,0) Sorry again for insufficient testing. -- Bob Quintal PA is y I've altered my email address. Jun 1 '06 #16

 P: n/a Bob Quintal wrote in news:Xn**********************@207.35.177.135: "Mr.Kane" wrote in news:11**********************@i40g2000cwc.googlegr oups.com: Everything looks good, however it seems that the records from Dec 2004 with dates less or equal to the 15th are defaulting to "YYYY00" ie record date entered 12/5/2004 = "200400" record date entered 12/11/2005 = "200500" record date entered 12/2/2004 = "200400" any additonal help would be appreciated (I'll try and tweak the expression and see if I can resolve the conflict as well) MonthOpened: Year([calldate])*100+IIf(Month([calldate])=12 And Day([calldate])>15,100,0)+(Month([calldate])+IIf(Day ([calldate])>15,1,0)) Mod 12+IIf(Month([calldate])=12 And Day ([calldate])<=15,12,0) Sorry again for insufficient testing. that one has a bug too. this seems to work right MonthOpened: Year([calldate])*100+IIf(Month([calldate])=12 And Day([calldate])>15,100,0)+(Month([calldate])+IIf(Day ([calldate])>15,1,0))-IIf(Month([calldate])=12 And Day ([calldate])>15,12,0) -- Bob Quintal PA is y I've altered my email address. Jun 1 '06 #17

 P: n/a Thanks again Bob... Jun 1 '06 #18