Is it possible....

  • Thread starter Thread starter Jacob
  • Start date Start date
J

Jacob

I have a report. In one field of the report, it is bringing back a number.
Where the number is, I would like to replace it with a text I tried the IF
THEN ELSE but did not work. Is there a way to use the SELECT CASE?
 
Here is a copy of what I tried...

If Me.pmmonth.Value = 1 Then
Me.pmmonth.Value = "Jan"
ElseIf Me.pmmonth = 2 Then
Me.pmmonth = "Feb"
ElseIf Me.pmmonth = 3 Then
Me.pmmonth = "Mar"
ElseIf Me.pmmonth = 4 Then
Me.pmmonth = "Apr"
ElseIf Me.pmmonth = 5 Then
Me.pmmonth = "May"
ElseIf Me.pmmonth = 6 Then
Me.pmmonth = "Jun"


End If
 
I'll take a wild guess that you are trying to display the first three
characters of month names.

Depending on what the underlying field is (e.g., a date/time field), you
could simply use the Format() function to return the three-character
monthname without saving/storing pmmonth.Value at all!

Good luck

Regards

Jeff Boyce
Microsoft Office/Accesss MVP
 
First, you need to be referencing the field that contains the data, not the
control bound to the field. For what you want, you can do this in the
Control Source of the control:

=Choose([pmmonth], "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec")

Where [pmmonth] is the name of the report's recordset field.
 
Jacob said:
I have a report. In one field of the report, it is bringing back a number.
Where the number is, I would like to replace it with a text I tried the IF
THEN ELSE but did not work. Is there a way to use the SELECT CASE?
Click to expand...


I'll chime in with a third approach, if what you want is to translate a
month number into a month name. You can use the MonthName function. It can
give you either the full name or an abbreviation, depending on an optional
argument. For example,

?MonthName(11)
November
?MonthName(11, true)
Nov
 
This seems right. However, I may not know how to place this code in. I have
never used the MonthName function. May I ask you how I would place that in
my code? Sorry for such a novice question, I am self taught in Access and
learning every day. I am also sorry it took me so long to get back to this.
 
Jacob said:
This seems right. However, I may not know how to place this code in. I
have never used the MonthName function. May I ask you how I would place
that in my code? Sorry for such a novice question, I am self taught in
Access and learning every day.
Click to expand...

We're always glad to teach. We don't have much information to answer your
question, though. I understand from your posts in this thread that you're
working with a report, and that there is a field named "pmmonth", which is a
month number 1-12. You want to display the (abbreviated) name of the month
on the report instead of the number.

I'm going to guess that you currently have a text box on the report that has
"pmmonth" as its ControlSource property. Quite likely the text box is also
named "pmmonth". We're going to change its ControlSource property, and also
its Name property. Change those properties as follows:

Control Source: =MonthName([pmmonth])
Name: txtPMMonthName

That ought to do it -- you don't have to write any VBA code of your own.

Incidentally, the reason you have to change the name is that it would be
confusing to Access if you had a control named "pmmonth" which wasn't bound
directly to the pmmonth field. There would then be two wholly distinct
things named "pmmonth", and Access would not know which was meant in any
context.

Please let me know if that solves your problem.
 
Back
Top