Display date in uppercase in subform.

[calvinle]

Hi,

I have set the table format to ddmmmyyyy and field in subform to ddmmmyyyy however I cant make it display in Uppercase..!

I have tried to set the field mask input to > but it does not reconigze as it's a date.
I tried after update event of the field to ucase but it does not work, however if I do the same code to an unbound textbox, it does work.

Can anyone help? I just need it display in ucase, the actual data in table does not need to be store in ucase.

Thanks.

 

[AccessBlaster]

See if this helps. http://webcheatsheet.com/SQL/access_functions/ucase.php

 

[calvinle]

I did use ucase but it deosnt work. Because the data in the field date is in format yyyy-Me may-dd i think thats why

 

[AccessBlaster]

I missed that sorry.

 

[CJ_London]

assuming your field is not updateable on the form then in your form recordsource query try

myDateField: UCase(format(somedate("ddmmmyyyy"))

 

[calvinle]

My subform is set to datasheet view and the data field has a calendar picker. Whenever the date is chosen, I set the format to: ddmmmyyyy which shows 03-oct-2015.

 

[CJ_London]

 I set the format to: ddmmmyyyy which shows 03-oct-2015

I would expect it to be showing 03Oct2015

I presume your field in the table is a date/time field?

how you format it in the table is immaterial - format properties are not carried forward to queries, forms or reports, except so far as when it creates the relevant control it will set the format property to the same as in the table.

What you require are two format actions, one to set the date format and one to make any text uppercase. The only way you can do this is to format the date using the format function (not the format property), which creates a new string value, then format the new value as uppercase, either as I have suggested or using the format property for the control.

 

[calvinle]

Too much formatting here Im a bit confuse.
So ur saying that I should remove the format in my table to nothing and the field format should be Date/Time? Also remove the format in the subform field to nothing?
So now the date shown in subform is : 2015-10-01

In the afterupdate event of the field in the subform (mydate), I place the code:

mydate=Ucase(format(mydate,"ddmmmyyyy"))

But after updating it shows: The value entered isnt valid for the field.

 

[CJ_London]

So ur saying that I should remove the format in my table to nothing

it doesn't matter what the format in the table is - it is only relevant when viewing data in the table

the field format should be Date/Time

no - the field type should be date/time

mydate=Ucase(format(mydate,"ddmmmyyyy"))

Assuming your control is bound then this would not work - I said

in your form recordsource query

 

[spikepl]

My subform is set to datasheet view and the data field has a calendar picker.

You have to choose: you display date in the format windows lets you, and then you can input a date in that fields also using the datepicker, OR you do what CJ_London advised, but then you cannot input a date easily.

I would not recommend going against the standard date display: if that is the how it is set then that is the way it displays in all applications, also Excel.

 

[calvinle]

So that being said, there is no way to display upper case of a bound control date in subform?

 

[CJ_London]

if that control is bound to a date/time field - no

 

[leanpilar]

UCase(Format(Now(), "dd.MMMM.yyyy"))