Grouping on date field

[Steve J]

I i need to group by month and year on a date field - is this possible
please?
S

 

[tina]

you could group on a calculated field, as

CalcDate: Format([MyDateField],"mm\/yy")

hth

 

[Dennis]

You could create a column in your query and then group on it.

YearMon: Year([DateField]) & Month([DateField])

 

[Steve J]

That's great dennis - thanks

is it possibl;e to format the month so that they are displayed in correct
order - persumably by adding a zero if konth number < 10

You could create a column in your query and then group on it.

YearMon: Year([DateField]) & Month([DateField])

I i need to group by month and year on a date field - is this possible
please?
S

 

[Steve J]

Sorted !

Thanks

That's great dennis - thanks

is it possibl;e to format the month so that they are displayed in correct
order - persumably by adding a zero if konth number < 10

You could create a column in your query and then group on it.

YearMon: Year([DateField]) & Month([DateField])

I i need to group by month and year on a date field - is this possible
please?
S

 

[Van T. Dinh]

It may be quicker to use Tina's suggestion since her suggestion involves
only 1 VBA function execution while Dennis' suggestion involves 2 VBA
function executions, 2 type-casting processes (from numeric to Text) & the
concatenating process. This, of course, hardly noticeable if you have only
small number of Records.

Try Tina's suggestion but use the formating String "yyyymm" instead, i.e.

YearMonth: Format([MyDateField],"yyyymm")


--
HTH
Van T. Dinh
MVP (Access)

That's great dennis - thanks

is it possibl;e to format the month so that they are displayed in correct
order - persumably by adding a zero if konth number < 10

You could create a column in your query and then group on it.

YearMon: Year([DateField]) & Month([DateField])

I i need to group by month and year on a date field - is this possible
please?
S

 

[tina]

you're right, Van, yyyymm will allow for a better sort order, thanks for the
tweak!

It may be quicker to use Tina's suggestion since her suggestion involves
only 1 VBA function execution while Dennis' suggestion involves 2 VBA
function executions, 2 type-casting processes (from numeric to Text) & the
concatenating process. This, of course, hardly noticeable if you have only
small number of Records.

Try Tina's suggestion but use the formating String "yyyymm" instead, i.e.

YearMonth: Format([MyDateField],"yyyymm")


--
HTH
Van T. Dinh
MVP (Access)

That's great dennis - thanks

is it possibl;e to format the month so that they are displayed in correct
order - persumably by adding a zero if konth number < 10

You could create a column in your query and then group on it.

YearMon: Year([DateField]) & Month([DateField])

:

I i need to group by month and year on a date field - is this possible
please?
S

 

[Steve J]

Thanks everyone

you're right, Van, yyyymm will allow for a better sort order, thanks for
the
tweak!

It may be quicker to use Tina's suggestion since her suggestion involves
only 1 VBA function execution while Dennis' suggestion involves 2 VBA
function executions, 2 type-casting processes (from numeric to Text) &
the
concatenating process. This, of course, hardly noticeable if you have only
small number of Records.

Try Tina's suggestion but use the formating String "yyyymm" instead, i.e.

YearMonth: Format([MyDateField],"yyyymm")


--
HTH
Van T. Dinh
MVP (Access)

That's great dennis - thanks

is it possibl;e to format the month so that they are displayed in correct
order - persumably by adding a zero if konth number < 10

You could create a column in your query and then group on it.

YearMon: Year([DateField]) & Month([DateField])

:

I i need to group by month and year on a date field - is this possible
please?
S