Format date

[Mike Hughes]

A table I'm working with has a field PERIOD_YYYYMM which returns 201104
is there a way to format it to return as April 2011?

Thanks

 

[DCrake]

Yes

Add 01 to the 201104 to get 20110401 then convert to date format and apply the mmmm yyyy format to it.

 

[JANR]

From the immediate window:

Format(Date, "mmmm yyyy") gives you april 2011

StrConv(Format(Date, "mmmm yyyy"),3) gives you April 2011

JR

 

[Mike Hughes]

In checking the table I'm doing the query against, the PERIOD_YYYYMM is a text field so I'm not sure if your response with work in this case. Here is the query. Any other ideas?


SELECT
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE,
NOLDBA_CASE_ASSIST_TYPE.PERIOD_YYYYMM,
NOLDBA_CASE_ASSIST_TYPE.CASE_TYPE,
NOLDBA_CASE_ASSIST_TYPE.ESTABLISH_TYPE,
Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS [Case Count]

FROM NOLDBA_INT_CASE_STATUS INNER JOIN NOLDBA_CASE_ASSIST_TYPE ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_CASE_ASSIST_TYPE.ID_CASE

GROUP BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_CASE_ASSIST_TYPE.PERIOD_YYYYMM, NOLDBA_CASE_ASSIST_TYPE.CASE_TYPE, NOLDBA_CASE_ASSIST_TYPE.ESTABLISH_TYPE, NOLDBA_INT_CASE_STATUS.CASE_STATUS

HAVING
(((NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE)=[DISTRICT OFFICE]) AND
((NOLDBA_CASE_ASSIST_TYPE.PERIOD_YYYYMM)=[FOR YYYYMM]) AND
((NOLDBA_CASE_ASSIST_TYPE.CASE_TYPE) In ("A","F","N","M","X")) AND
((NOLDBA_CASE_ASSIST_TYPE.ESTABLISH_TYPE) In ("O","P","S")) AND
((NOLDBA_INT_CASE_STATUS.CASE_STATUS) In ("O","I")))

ORDER BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_CASE_ASSIST_TYPE.CASE_TYPE, NOLDBA_CASE_ASSIST_TYPE.ESTABLISH_TYPE;

 

[JANR]

It is going to be ugly!

In the design grid create a new calculated field.

NewField: Format(DateSerial(Left([Period_yyyymm],4), Mid([period_yyyymm],5,2), "01"), "mmmm yyyy")

JR

 

[Mike Hughes]

That's it. Thanks very much!

Mike