Seggregation of records based on "Start date" field

[sgowri7]

Hi there,

I have a table which has numerous records and each have a datetime value in the StartDate field. I would like to assign a month (not the calender month) to it based on the date value present in this field.


if the start date is between 12/26 and 01/25, then I should get 1101Jan in the Yearmonth field for the record.

seggregation criteria is:

StDtMth EndDtMth YearMonth
12/26/10 0:00:00 01/25/11 23:59:59 1101Jan
01/26/11 0:00:00 02/25/11 23:59:59 1102Feb
02/26/11 0:00:00 03/25/11 23:59:59 1103Mar
03/26/11 0:00:00 04/25/11 23:59:59 1104Apr
04/26/11 0:00:00 05/25/11 23:59:59 1105May
05/26/11 0:00:00 06/25/11 23:59:59 1106Jun
06/26/11 0:00:00 07/25/11 23:59:59 1107Jul
07/26/11 0:00:00 08/25/11 23:59:59 1108Aug
08/26/11 0:00:00 09/25/11 23:59:59 1109Sep
09/26/11 0:00:00 10/25/11 23:59:59 1110Oct
10/26/11 0:00:00 11/25/11 23:59:59 1111Nov
11/26/11 0:00:00 12/25/11 23:59:59 1112Dec
11/25/11 0:00:01 12/25/11 0:00:00 1112Dec


How will this be brought into a query? Please help.

 

[Joe4]

I believe using this calculated field in a query will give you what you need:

MyMonth:IIF(Day([DateField])<26,Format([DateField],"yymm") & Format([DateField],"mmm"),Format([DateField]+6,"yymm") & Format([DateField]+6,"mmm"))

 

[sgowri7]

Hello Joe,

Gr8. It worked. Thank you.

Regs,
SGS