Group Date

naungsai

Abecedarian
Local time
Today, 20:12
Joined
Sep 8, 2008
Messages
123
Dear Friends

I have a table with a "Date" field. In the queiry, I have grouped it into 6 monthly. Although it is easy to group the date in Quarterly, I do not find ready made expression for 6 monthly. So I have used the following expression:

6Monthly: IIf([Date]<#1/1/2008#,"1stSixMonth",IIf([Date]<#7/1/2008#,"2ndSixMonth",IIf([Date]<#1/1/2009#,"3rdSixMonth",IIf([Date]<#7/1/2009#,"4thSixMonth","5thSixMonth"))))

You may notice that the above expression is quite limited. It can only group the date between 6/1/2007 to 12/31/2009. If I want to make it further qrouping, I need to modify the experssion.

Here, my question is "is there more decent VBA code for this stuff?". I want to start from 6/1/2007 onward.:p

Thank in advance
 
How about not having to use VB at all? Put your "periods" in a Periods table, join to the Periods table in your query, and only query on the periods you are interested in.
 
your question belongs to queries section
yet'll help for this one time hopeing you will diffrentiate later.

1st rename [Date] field to a name that is not reserved ex [my_date]

2ns you need to group on two fields (Month - Year) so here goes the interval expression :

Interval : IIF(Month([my_date])>=1 and Month([my_date])<=6;(((Year([my_date])-2007)*2)+1) & IIF((((Year([my_date])-2007)*2)+1)=1;"st";IIF((((Year([my_date])-2007)*2)+1)=3;"rd";"th")) & "SixMonth";(((Year([my_date])-2007)*2)+2) & IIF((((Year([my_date])-2007)*2)+2)=2;"nd";"th") & "SixMonth")

and

where ([my_date]) >= #6/1/2007#

and you are good to go
 
Dear Night Mayor

Thank you. It works.
In my table, I do use another name for the "date".
As you say it should be put in Query. But I hope that only VB will solve my question, so I have put it in this catagory. Sorry for mis-place.;)
 
heh i just revised the code and saw there was no real need for Month([my_date])>=1 so you can strike it out..

Code:
Interval : IIF(Month([my_date])<=6;(((Year([my_date])-2007)*2)+1) & IIF((((Year([my_date])-2007)*2)+1)=1;"st";IIF((((Year([my_date])-2007)*2)+1)=3;"rd";"th")) & "SixMonth";(((Year([my_date])-2007)*2)+2) & IIF((((Year([my_date])-2007)*2)+2)=2;"nd";"th") & "SixMonth")
 
play more sudoku , it helps sharpening the mind for playing with date fields :p
 
heh i just revised the code and saw there was no real need for Month([my_date])>=1 so you can strike it out..

Code:
Interval : IIF(Month([my_date])<=6;(((Year([my_date])-2007)*2)+1) & IIF((((Year([my_date])-2007)*2)+1)=1;"st";IIF((((Year([my_date])-2007)*2)+1)=3;"rd";"th")) & "SixMonth";(((Year([my_date])-2007)*2)+2) & IIF((((Year([my_date])-2007)*2)+2)=2;"nd";"th") & "SixMonth")

Dear nIGHTmAYOR:D

Thank you for your reply. Through your expression, I have learnt alot. I have written an expression as followed.

Code:
Interval: Format([my_date],"yyyy") & IIf(Month([my_date])>6,2,1)/code]
 
Thanks again for putting some concept of writing expression in yours.
 
Best:D
 
heh i just revised the code and saw there was no real need for Month([my_date])>=1 so you can strike it out..

Code:
Interval : IIF(Month([my_date])<=6;(((Year([my_date])-2007)*2)+1) & IIF((((Year([my_date])-2007)*2)+1)=1;"st";IIF((((Year([my_date])-2007)*2)+1)=3;"rd";"th")) & "SixMonth";(((Year([my_date])-2007)*2)+2) & IIF((((Year([my_date])-2007)*2)+2)=2;"nd";"th") & "SixMonth")

Dear nIGHTmAYOR:D

Thank you for your reply. Through your expression, I have learnt alot. I have written an expression as followed.

Code:
Interval: Format([my_date],"yyyy") & IIf(Month([my_date])>6,2,1)

Thanks again for putting some concept of writing expression in yours.

Best:D
 
I prefer George's solution, it is simple to maintain and extend, not like all those slow and nasty hardcoded IIfs

Brian
 
Brian , the code was simply to learn from and to cover a dare :)
 

Users who are viewing this thread

Back
Top Bottom