Convert Numeric Field [yearmonthvalue] to Text on Report

  • Thread starter Thread starter mlieberstein
  • Start date Start date
M

mlieberstein

I have a textbox, txtYMV, on my form, frm_BusinessGroup, where the user
enters yyyymm value--i.e., 200906.

So on my report, BusinessGroupReport_rpt, I have a text box that says:
=Forms!frm_BusinessGroup!txtYMV, and I get back that value.

However I'd like to show the yyyymm value [200906] on the report as June
2009, I'd like to have an expression where:

If Right(txtYMV,2)= 01, then January and field [Year]
If Right(txtYMV,2)= 02, then February and field [Year]
If Right(txtYMV,2)= 03, then March and field [Year], etc.

Any ideas?
 
Try convert the value to a date and use the format property:

Control Source: =DateSerial(Val(Left(Forms!frm_BusinessGroup!txtYMV,4)),
Val(Right(Forms!frm_BusinessGroup!txtYMV,2)),1)
Format: mmmm yyyy
 
mlieberstein said:
I have a textbox, txtYMV, on my form, frm_BusinessGroup, where the user
enters yyyymm value--i.e., 200906.

So on my report, BusinessGroupReport_rpt, I have a text box that says:
=Forms!frm_BusinessGroup!txtYMV, and I get back that value.

However I'd like to show the yyyymm value [200906] on the report as June
2009, I'd like to have an expression where:

If Right(txtYMV,2)= 01, then January and field [Year]
If Right(txtYMV,2)= 02, then February and field [Year]
If Right(txtYMV,2)= 03, then March and field [Year], etc.
Click to expand...


Not a good way to save a date.

Set the text box's expression to this kind of thing:

=DateSerial(Left(txtYMV,4), Right(txtYMV,2), 1)

And then set the text box's Format property to:
mmmm yyyy
 
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Marshall said:
mlieberstein said:
I have a textbox, txtYMV, on my form, frm_BusinessGroup, where the user
enters yyyymm value--i.e., 200906.

So on my report, BusinessGroupReport_rpt, I have a text box that says:
=Forms!frm_BusinessGroup!txtYMV, and I get back that value.

However I'd like to show the yyyymm value [200906] on the report as June
2009, I'd like to have an expression where:

If Right(txtYMV,2)= 01, then January and field [Year]
If Right(txtYMV,2)= 02, then February and field [Year]
If Right(txtYMV,2)= 03, then March and field [Year], etc.
Click to expand...


Not a good way to save a date.

Set the text box's expression to this kind of thing:

=DateSerial(Left(txtYMV,4), Right(txtYMV,2), 1)

And then set the text box's Format property to:
mmmm yyyy
Click to expand...
 
As long as txtYMV is never null, you should be able to use

Format(Cdate(Format(txtYMV,"@@@@-@@")), "mmmm yyyy")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Marshall said:
mlieberstein said:
I have a textbox, txtYMV, on my form, frm_BusinessGroup, where the user
enters yyyymm value--i.e., 200906.

So on my report, BusinessGroupReport_rpt, I have a text box that says:
=Forms!frm_BusinessGroup!txtYMV, and I get back that value.

However I'd like to show the yyyymm value [200906] on the report as June
2009, I'd like to have an expression where:

If Right(txtYMV,2)= 01, then January and field [Year]
If Right(txtYMV,2)= 02, then February and field [Year]
If Right(txtYMV,2)= 03, then March and field [Year], etc.
Click to expand...


Not a good way to save a date.

Set the text box's expression to this kind of thing:

=DateSerial(Left(txtYMV,4), Right(txtYMV,2), 1)

And then set the text box's Format property to:
mmmm yyyy
Click to expand...
 
Perrrrfect! thank you, it looks great :)

Also, thanks to everyone else who responded.

Duane Hookom said:
Try convert the value to a date and use the format property:

Control Source: =DateSerial(Val(Left(Forms!frm_BusinessGroup!txtYMV,4)),
Val(Right(Forms!frm_BusinessGroup!txtYMV,2)),1)
Format: mmmm yyyy
--
Duane Hookom
Microsoft Access MVP


mlieberstein said:
I have a textbox, txtYMV, on my form, frm_BusinessGroup, where the user
enters yyyymm value--i.e., 200906.

So on my report, BusinessGroupReport_rpt, I have a text box that says:
=Forms!frm_BusinessGroup!txtYMV, and I get back that value.

However I'd like to show the yyyymm value [200906] on the report as June
2009, I'd like to have an expression where:

If Right(txtYMV,2)= 01, then January and field [Year]
If Right(txtYMV,2)= 02, then February and field [Year]
If Right(txtYMV,2)= 03, then March and field [Year], etc.

Any ideas?
Click to expand...
Click to expand...
 
Back
Top