Convert Numeric Field [yearmonthvalue] to Text on Report

[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?

 

[Duane Hookom]

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

 

[Marshall Barton]

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.

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]

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

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.

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]

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

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.

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

 

[mlieberstein]

Perrrrfect! thank you, it looks great

Also, thanks to everyone else who responded.

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

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?