Date/day format

[dasadler]

In excel, I can have a date in cell A1 then have B1=A1 except that A1 can be formatted as a date and B1 can be formatted as a day. Is this possible in Access? I have form with a date field and when I click on the date field a little calendar pops up so I can select the date. I would like to have another field that shows the day of the date field but not to have a calendar control itself... just display the day of the date selected in the date field.

[Rudi]

Hi dasadler,

One of two ways for this:

1. Create a new text box control and type this formula in it: =DAY([Date_Control_Name]) - This displays the day portion of the date in the date control. Just replace "Date_Control_Name" with the name of the date control on the form.

2. Copy the date control and paste it onto the form as a copy and then right-click on the control and choose "Change To" and select text box. In the text box, type =[Date_Control_Name] to populate the control with the value from the date control. Open design view and format the control to "dd" (without the quotes). This will display the date in day mode only. Type 3d's (or 4d's) for Text version of day if you want.

[HansV]

Create a text box.
Set the Control Source to the date field.
Set the Format property to one of the following custom formats:
d = day number 1-31
dd = day number with leading zero 01-31
ddd = abbreviated name of day Sun-Sat
dddd = full name of day Sunday-Saturday
Set the Show Date Picker property of the text box to Never.
You may want to set the Locked property of the text box to Yes and the Enabled property to No.

[dasadler]

Thank you both for your help.

Hans - what would be the purpose/benefit of setting the Locked property of the text box to Yes and the Enabled property to No?

[HansV]

If the text box wouldn't be locked, the user would be able to edit the date there - I assumed that you only wanted to use it to display the day, not to edit the date.

[dasadler]

That was a correct assumption. What about 'enabled'?

[HansV]

That's not strictly necessary, but I think it's less confusing to the users if they can't click in a control that they can't edit anyway.

[Rudi]

The two work together to prevent clicking in the control and editing the value.

Enabled: Disables the control completely - it turns gray to indicate it is "off"
Locked: Locks the control so that it cannot be edited (but the user can still click in it and position the cursor somewhere)
Using both: Locks the control so it can't be edited but also disables it so the user cannot click in it. Using both together also prevents the control from turning gray.

[dasadler]

Got it. Thank you. I agree with not enabling the field.

FWIW, prior to seeing your responses, I was working with the field both enabled and not enabled. I found that when enabled, not only could I select the field but that the contents of the field would change from ddd to some date like 1/25/1900. For example, in one case the date value was 9/12/2009 the the text field changed from Thu to 1/11/1900 when I select it. When I moved the cursor to another field, it went back to Thu.

[Rudi]

This is the advantage of disabling and locking the control. It will prevent the control showing the unformatted content if you cannot click in it. Controls show the formatted value until you click in it...then it shows the unformatted value. You see it often in queries on calculated fields. When it is formatted say in currency, you see the currency symbol, but when you click in it then it shows as general, without the symbol.

[dasadler]

Makes sense but I would think the unformatted value would be the same as the control source (the date field).

[HansV]

Here is an example of what Rudi means. You see text boxes - the one on the left shows the price of an article in Euros, the one on the right the price converted to Dutch Guilders (the currency we used before 2002).

In the upper half, the focus is on another control, and the Guilders text box shows the currency unit and the amount rounded to two decimal places.
In the lower hald, the Guilders text box has the focus, and you see the raw converted value, without the currency unit, and not rounded.

x.png

[dasadler]

Okay, I understand that however, in your example (honestly, I don't mean to be dense here) the formatting changed but the underlying value is the same (24,4612). In my observation, the unformatted value was 1/11/1900 instead of 9/12/2009; a totally different date value.

[HansV]

What exactly did you use as the Control Source of the text box, and what did you set as Format?

[dasadler]

Ah... therein lies the problem. I had the control source set as =DAY([EventDate]) instead of simply [EventDate]. Now, when I select it, it changes from Thur to 9/12/2009.

Thanks for your patience.

[HansV]

Yep, that's it!