How to use UCase for date format?

I'd like to get my date format(mmmm dd","yyyy) is show in upper case likes JANUARY 2, 2000. I have tried to use the Ucase function but it doesn't work!
Here is my info.
the fileld name (in Check_Entry Form) is txtDate, format is mmmm dd","yyyy. Now it show January 12,2000 but I need JANUARY 12, 2000. Pls help! Thks
jonjarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DedushkaCommented:
Sub Test()
    MsgBox StrConv(Format(Date, "mmmm dd, yyyy"), vbUpperCase)
End Sub
0
paaskyCommented:
Hello jonjar,

Change txtDate field control source to

=Ucase(Format([txtDate];"mmmm dd, yyyy"))

You can't use Ucase function in format property.

Regards,
Paasky




0
DedushkaCommented:
jonjar,
my first comment was for test purposes, so you can use this formula as control source for your textbox:

= StrConv(Format([txtDate], "mmmm dd, yyyy"), vbUpperCase)

Regards,
Dedushka

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jonjarAuthor Commented:
Hi Paasky
 I've tried as your instruction. It's O.K. but I can't edit the field. Let's me say more detail what I need.
I using Bill Table , that included date filed in the table, to created the Bill of Exchange Form so the control source of my txtDate filed in the form should be Date, right? I have change your formula as =Ucase(Format([Date];"mmmm dd, yyyy")) because my Control source is Date (that come from the Bill Table).It's work! but I can't edit the filed. So I need this textbox showing the UpperCase of date entry and can edit it.  My textbox name is txtDate that I need this textbox for date entry in the form. Do you understand my point? Pls help!
0
jonjarAuthor Commented:
Adjusted points to 125
0
jonjarAuthor Commented:
Hi Paasky
 I've tried as your instruction. It's O.K. but I can't edit the field. Let's me say more detail what I need.
I using Bill Table , that included date filed in the table, to created the Bill of Exchange Form so the control source of my txtDate filed in the form should be Date, right? I have change your formula as =Ucase(Format([Date];"mmmm dd, yyyy")) because my Control source is Date (that come from the Bill Table).It's work! but I can't edit the filed. So I need this textbox showing the UpperCase of date entry and can edit it.  My textbox name is txtDate that I need this textbox for date entry in the form. Do you understand my point? Pls help!
0
paaskyCommented:
jonjar,

I think this is a little more complicated now but I have a work around suggestion:

1. Set TabStop and Enabled of uppercase formatted Date textbox to False, and Locked to True
2. Create new textbox which control source is Date, set it's format mask to mmmm dd","yyyy
3. Place new textbox just at the same position with Ucase formatted textbox and adjust their size same.
4. Send new textbox to Back (Format-Send to Back)

Voila! Got the idea?

Now the user sees upper case date and when he/she enters date field, he can edit date (formatting doesn't show anyway when the user enters field) with the "hidden" Date textbox.

Hope this helps,
Paasky
0
jonjarAuthor Commented:
Adjusted points to 130
0
jonjarAuthor Commented:
Hi Paasky ,
 Thks for your info. I've tried to do as your guide and I can see the upper case date on that textbox but I still can't edit that textbox. I must create new textbox that controls source is date and I must do date entry on this textbox and then I will show the upper case date on the textbox that your instruction. Could you pls describe me more detail. I'll giving you a points asap. Thks!
0
DedushkaCommented:
Hi jonjar,

unfortunately you can't do you want with bounded textbox.
But you can do this using this approach:
1. Make your textbox txtDate unbounded
2. In onCurrent event for the form use:

Private Sub Form_Current()
Me.txtDate = StrConv(Format([fldDate], "mmmm dd, yyyy"), vbUpperCase)
End Sub

Here [fldDate] is field from the table and its value will be formatted and assigned to the textbox on the form.

3. In the OnAfterUpdate event for the textbox use:

Private Sub txtDate_AfterUpdate()
Dim tempDate As Date

tempDate = CVDate(Me.txtDate)
[fldDate] = tempDate
Me.txtDate = StrConv(Format([tempDate], "mmmm dd, yyyy"), vbUpperCase)
End Sub

so if you type valid date (01.01.2000 for example) into your textbox, its value will be converted to date and assign to the field in the table then formatted and display as "JANUARY 01, 2000".

Best regards,
Dedushka
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jonjarAuthor Commented:
Adjusted points to 135
0
jonjarAuthor Commented:
Hi Dedushka ,
 It's great!! may I ask you more question? I'd like to have upper case date on the table too! Can I do it since my report need to use the upper case date. Pls advise ASAP.
Thks & Best regards,
0
paaskyCommented:
jonjar,

You can't store date values Uppercase, because the date is actually stored as real (number). Maybe you should add a new text type field into your table if you really need to see it uppercase in the table. When you insert / update Date field the text field is populated with uppercase formatted value.

(modified Dedushka's example a bit)

Private Sub txtDate_AfterUpdate()
Dim tempDate As Date

tempDate = CVDate(Me.txtDate)
[fldDate] = tempDate
Me.txtDate = StrConv(Format([tempDate], "mmmm dd, yyyy"), vbUpperCase)
Me.newTxtDate = StrConv(Format(Me.Me.txtDate, "mmmm dd, yyyy"), vbUpperCase)

End Sub

Regards,
Paasky
0
paaskyCommented:
Remove one Me. from the example... it should be like this

Me.newTxtDate = StrConv(Format(Me.txtDate, "mmmm dd, yyyy"), vbUpperCase)
0
jonjarAuthor Commented:
Adjusted points to 140
0
jonjarAuthor Commented:
Hi Paasky,
As you said it can't store the upper case date in the table, just in case I need to make a report and need it print out to upper case, Can I do it?
Pls advise again
Best regards,
0
DedushkaCommented:
paasky,
thank you for good cooperation.

jonjar,
why do you want to store dates in the table in text format? You can show them in the forms (and reports) in any format you need, so you should store dates in DateTime format, it takes less memory and allow you to do some calculations as difference between dates and so on...

Regards,
Dedushka
0
paaskyCommented:
I totally agree with Dedushka. Use instructions above with forms and

=Ucase(Format([txtDate];"mmmm dd, yyyy"))

or

=StrConv(Format([txtDate], "mmmm dd, yyyy"), vbUpperCase)

with reports.

Paasky
0
jonjarAuthor Commented:
Dear Paasky,
 Could you pls tell me how can I show date in Uppercase in my report? where can I put your command?
Pls kindly let me know
Thks
0
paaskyCommented:
jonjar,

You just need to set textbox Control Source property to value introducted above:

instead of

Date Field Name

use

=Ucase(Format([Date Field Name];"mmmm dd, yyyy"))

Hope this helps
Paasky
0
jonjarAuthor Commented:
Dear Paasky,
Thank you very much for your big help. I'd like to be a expert guy likes you in the next time! :)
 May I asked you more question, pls?
I have a combo box that contain currency code and currency name such as USD | U.S. Dollars
I'd like to store only currency name in my table and need my form and report show the rest filed of combo, currency code, automatically once I select the currency name! can I do that? Pls kindly help!
Best regards,

P.S. I have posted my previous question at the question page so pls go to give me any comment so I can give you the point ASAP.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.