Display Text based on Report Field Value

[sherlocked]

Hello experts,

I have a report I'm designing that is grouped by month. The field from the DB that contains the month is a number field, 1 for January, 2 for February and so on.

I'm grouping my report by this field, called [ExecMonth].

I'd like to display the month name based on the value of this field in my group header. I have an unbound field called [txtMonth] and I'm using the below CASE statement to attempt to display the correct information.

What's happening, for some reason I can't determine, is the report is displaying "February" for every record on my report, even though I have records where the [ExecMonth] field is "1" and should show as "January."

What am I doing wrong? Your advice, as always, is greatly appreciated.

Select Case Me.txtExecMonth
    Case "1"
    Me.txtMonth = "January"
    Case "2"
    Me.txtMonth = "February"
    Case "3"
    Me.txtMonth = "March"
    Case "4"
    Me.txtMonth = "April"
    Case "5"
    Me.txtMonth = "May"
    Case "6"
    Me.txtMonth = "June"
    Case "7"
    Me.txtMonth = "July"
    Case "8"
    Me.txtMonth = "August"
    Case "9"
    Me.txtMonth = "September"
    Case "10"
    Me.txtMonth = "October"
    Case "11"
    Me.txtMonth = "November"
    Case "12"
    Me.txtMonth = "December"
End Select

 

[isladogs]

If its a number field you don't need the '' in the case statement.
Try Case 1 etc

 

[pbaldy]

Where is the code? It would need to be in the group header format event. I'd probably have a table with the values, and join it in the source query.

 

[isladogs]

I would also suggest using s table with 2 fields: month number & month name

Alternatively just use one of the functions built into Access to do the conversion for you

 

[sherlocked]

Now THAT is using the K.I.S.S method! Worked like a charm. Many thanks!