Format a Char field to show a formated date? 2

[ordendelfai]

I have a field in a table that is a char, and holds data from the user and automated sources in "yyyy/dd" format. I could not use Date type because it forces the user to type in the day, no matter how I format it, and it stores the day of the date in the field, which I cannot allow.

On a report, I need a control that would normally disply 2003/08 from a table to instead display "Aug, 2003". I cannot figure out how to do this though because the field is a char and not a date. The good news I suppose is the data in the field will always be "yyyy/dd".

Maybe I am doing this all wrong, so if you have a better way, please let me know . Otherwise, if you could help with a way I could format it, would really appreciate ;o).

Thanks,

~Orden

 

[CosmoKramer]

Add an expression field to your query:

MonthYear: Format([YourTextDateField],"mmm"", ""yyyy")

Then reference this field in your report.

Hoc nomen meum verum non est.

 

[DougP]

Here is some Access 101
a Date Field in a table must have a date like so
8/23/03
if you want to see it different on a report then format it anyway you like

But this little function should do it for you
put it in a module

Public Function ConvertDate(MyDate)
MyYear = Left(MyDate, 4)
MyMonth = Choose(Right(MyDate, 2), "Jan", "Feb", "Mar", "Apr", "May&quot ''note add the rest of the monthsI stopped at May
ConvertDate = MyMonth & " " & MyYear
End Function

Call it like so
SomeDate = ConvertDate([Yourdate])
or
SomeDate = ConvertDate("2003/03&quot
SomeDate = Mar 2003



DougP, MCP

 

[ordendelfai]

Cosmo, thanks for trying, I tried that before and it doesn't work because the data is not in a data format.

Doug, your suggestion worked great, and I learned something new (the Choose function).

To clear up any confusion for others, in the actual control source of the text box on the report I put:

=ConvertDate([Year_Month])

Year_Month is the name of the field in the underlying table (or query), so you would have to change that to your field name.

Here's a star Doug ;o)

 

[CosmoKramer]

I did see that you noted it was a text field. That syntax works for me with text fields and I did re-test before I posted my response.

The important thing is you have a method that works for you.

Good luck!!

Hoc nomen meum verum non est.

 

[ordendelfai]

My mistake cosmo, you are correct. I was trying your method in the control on the form itself. Star for you because that will help me in another situation where code is not favorable.

Thx ;o).