Date/day format
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Date/day format
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.
Don
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Date/day format
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.
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/day format
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Date/day format
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?
Hans - what would be the purpose/benefit of setting the Locked property of the text box to Yes and the Enabled property to No?
Don
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/day format
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/day format
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.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Date/day format
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.
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Date/day format
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.
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.
Don
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Date/day format
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Date/day format
Makes sense but I would think the unformatted value would be the same as the control source (the date field).
Don
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/day format
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Date/day format
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.
Don
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date/day format
What exactly did you use as the Control Source of the text box, and what did you set as Format?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Date/day format
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.
Thanks for your patience.
Don
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands