Date/time format doesn't stick

[Carl Colijn]

Hi all,

The date/time fields in my tables do not have any input mask and/or notation
set; I have only set a notation to the corresponding text control on the
bound form. The notation gets set dynamically via VBA to be a custom
format; "d/m/yyyy h:nn" or "m/d/yyyy h:nn", depending on the user's regional
settings.

When the user enters date and time information, it all works well for most
date/time combinations. However, for some date/time combinations, the
display works OK when the user leaves the text control, but when the user
consecutively re-enters the text control, the format changes instantly to
standard date/time notation ("m/d/yyyy h:nn:ss AM/PM"). Such an "expanding"
date looks very weird and I'd like to get rid of it, mostly also because it
only occurs with a fraction of the records.

Some examples:
3/1/2006 13:00 gets expanded to 3/1/2006 1:00:00 PM, but
3/17/2006 13:00 does not get expanded
When I then change the 3/17/2006 to 3/1/2006, the expansion will occur
again.

The way to get this 'feature' to raise it's head (at least, in Access 2003,
Dutch Edition), is:
- create an empty database
- clear the "automatic name corruption" setting
- compact/repair
- create a table with one fields
- set it's type to Date/Time
- keep notation and input mask empty
- save the table
- create a form
- bind it to the above table
- add a text control bound to the date/time field
- keep it's notation and input mask empty
- make the default view of the form continuous (for easier testing)
- add the following VBA to the form's Form_Load event:
Text1.Format = "m/d/yyyy h:nn"
- save the form
- open the form
- enter:
3/17/2006 13:00
- go to another record, and go back to the above one; the format doesn't
change
- enter:
3/1/2006 13:00
- go to another record (again, nothing changes), and go back to the above
one; the content now changes to 3/1/2006 1:00:00 PM

I have searched Google for this behaviour, but came up empty-handed. If
anyone can reproduce this behaviour, then I will at least know it's not only
me seeing this problem. Of course, if someone knows the underlying cause
(or a workaround), that would be even better

Thanks in advance,
Carl Colijn

 

[Carl Colijn]

Because this message will probably not get answered in access.forms, I added
access.modulesdaovba to the thread to see if there are more knowledgeable
people there that might know what's going on with the date/time formatting.
Thanks in advance!

=========== Original message ===========

Hi all,

The date/time fields in my tables do not have any input mask and/or
notation set; I have only set a notation to the corresponding text
control on the bound form. The notation gets set dynamically via VBA
to be a custom format; "d/m/yyyy h:nn" or "m/d/yyyy h:nn", depending
on the user's regional settings.

When the user enters date and time information, it all works well for
most date/time combinations. However, for some date/time
combinations, the display works OK when the user leaves the text
control, but when the user consecutively re-enters the text control,
the format changes instantly to standard date/time notation
("m/d/yyyy h:nn:ss AM/PM"). Such an "expanding" date looks very
weird and I'd like to get rid of it, mostly also because it only
occurs with a fraction of the records.

Some examples:
3/1/2006 13:00 gets expanded to 3/1/2006 1:00:00 PM, but
3/17/2006 13:00 does not get expanded
When I then change the 3/17/2006 to 3/1/2006, the expansion will occur
again.

The way to get this 'feature' to raise it's head (at least, in Access
2003, Dutch Edition), is:
- create an empty database
- clear the "automatic name corruption" setting
- compact/repair
- create a table with one fields
- set it's type to Date/Time
- keep notation and input mask empty
- save the table
- create a form
- bind it to the above table
- add a text control bound to the date/time field
- keep it's notation and input mask empty
- make the default view of the form continuous (for easier testing)
- add the following VBA to the form's Form_Load event:
Text1.Format = "m/d/yyyy h:nn"
- save the form
- open the form
- enter:
3/17/2006 13:00
- go to another record, and go back to the above one; the format
doesn't change
- enter:
3/1/2006 13:00
- go to another record (again, nothing changes), and go back to the
above one; the content now changes to 3/1/2006 1:00:00 PM

I have searched Google for this behaviour, but came up empty-handed.
If anyone can reproduce this behaviour, then I will at least know
it's not only me seeing this problem. Of course, if someone knows
the underlying cause (or a workaround), that would be even better

Thanks in advance,
Carl Colijn

 

[strive4peace]

Hi Carl,

the FORMAT property will be in effect as long as the control
is not selected -- if it is the active control, you will see
the data that is there and the FORMAT property will have no
effect.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Carl Colijn]

In strive4peace" <"strive4peace2006 at yahoo dot com <"strive4peace2006 at
yahoo dot com"> typed:

Hi Carl,

the FORMAT property will be in effect as long as the control
is not selected -- if it is the active control, you will see
the data that is there and the FORMAT property will have no
effect.

Hi Crystal,

Then there is no way you can enforce a custom format on the content of a
control when you do not want to use an input mask? Or is this phenomenon
only seen in Access 2003, but not in previous versions?

Another good question would be why an active edit box with a custom format
property will obey this property for certain date/time combinations (e.g.
3/17/2006 13:00) while rejecting it for others (e.g. 3/1/2006 13:00) using
another (non specified!) format instead... All hints are welcome, since I
cannot think of any reason that could give rise to this behaviour (except
for a bug in Access itself).

The only logical train of thoughts I could come up with is that it might
have to do with internal rouding of the time part of the date/time value.
If some date/times could not be represented exactly (so that they e.g. could
not represent an exact hour and minute), then there could be a reason to
show the extra information via another format; the format Access enforces in
these circumstances also shows the seconds. But then Access still shows :00
seconds, and it doesn't explain why the time 13:00 gets expanded on one date
and not on another (after all: only the integral part changes in this
circumstance, but not the fractional). And to kick the last leg from under
this chair: 13:00 cannot be represented exactly in a binary format anyway.

Anyway, hope someone can think of a better explanation (or a workaround!),
Thanks in advance,
Carl

 

[strive4peace]

Hi Carl,

you can do this:

make the control a calculated field using the Format
property. Use the Double-click even to popup a calendar or
InputBox to allow them to modify or input the date -- then
change it through code.

I do have a popup calendar I can send to you that you could
modify to help you with this -- email me if you want it.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Carl Colijn]

In strive4peace" <"strive4peace2006 at yahoo dot com <"strive4peace2006 at
yahoo dot com"> typed:

Hi Carl,

you can do this:

make the control a calculated field using the Format
property. Use the Double-click even to popup a calendar or
InputBox to allow them to modify or input the date -- then
change it through code.

I do have a popup calendar I can send to you that you could
modify to help you with this -- email me if you want it.

Hi Crystal,

While it is a possible solution, I'm affraid it won't do in my circumstance.
The date/time field in question is one of many fields that are used by data
typists for quick data entry. If they will need to double-click the field
to change it, they will not be able to enter new data and change existing
data as fast as they do now.

I also thought about splitting the field in two; separate the date from the
time and see if that will make the format stick. But that will break the
flow of the form as well I'm affraid.

Guess I have to choose between several evils in this case

Anyway, thanks for the help!
Kind regards,
Carl

 

[strive4peace]

The other thing you can do, since you just want date and not
time, is make the control unbound. On the form OnCurrent event:

me.unbound_controlname = DateValue(me.datefieldname)

then, on the AfterUpdate event of your unbound control...

'~~~~~~~~~~~~~~~
if isnull(me.unboundcontrolname) then
me.datefieldname = null
else
me.datefieldname = me.unboundcontrolname
end if
'~~~~~~~~~~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Carl Colijn]

In strive4peace" <"strive4peace2006 at yahoo dot com <"strive4peace2006 at
yahoo dot com"> typed:

The other thing you can do, since you just want date and not
time, is make the control unbound. On the form OnCurrent event:

me.unbound_controlname = DateValue(me.datefieldname)

then, on the AfterUpdate event of your unbound control...

'~~~~~~~~~~~~~~~
if isnull(me.unboundcontrolname) then
me.datefieldname = null
else
me.datefieldname = me.unboundcontrolname
end if
'~~~~~~~~~~~~~~~

Hi Crystal,

Thanks for the suggestion, but I need to use both date and time information.
And since I use a continuous form, I cannot use unbound controls... (well, I
could of course, but it would look a bit confusing).

Kind regards,
Carl

 

[strive4peace]

Hi Carl,

so... is the only problem the way the information displays
on the activecontrol?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Carl Colijn]

In strive4peace" <"strive4peace2006 at yahoo dot com <"strive4peace2006 at
yahoo dot com"> typed:

Hi Carl,

so... is the only problem the way the information displays
on the activecontrol?

Hi Crystal,

Yes; when the control is not active, the information is displayed correctly
using my format property. But when the control is active, it uses it's own
format to display the information for some date/time combinations. I would
like the control to obey my custom format in all cases though.

Kind regards,
Carl

 

[strive4peace]

Hi Carl,

I do not think that is possible. If the date is first on
your tab order, you may want to change that ... I sometimes
make a dummy control on forms...

textbox:
Name --> Dummy
left --> 0
top --> 0
height --> .01
width --> .01

back color--> transparent
fore color --> same as your section back color

It is so small that the users do not really see it and you
can make it first on the TAB order

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Carl Colijn]

In strive4peace" <"strive4peace2006 at yahoo dot com <"strive4peace2006 at
yahoo dot com"> typed:

Hi Carl,

I do not think that is possible. If the date is first on
your tab order, you may want to change that ... I sometimes
make a dummy control on forms...

textbox:
Name --> Dummy
left --> 0
top --> 0
height --> .01
width --> .01

back color--> transparent
fore color --> same as your section back color

It is so small that the users do not really see it and you
can make it first on the TAB order

Hi Crystal,

Thanks again for the suggestion!

But in my case this is also not applicable. It's not that the date/time
field is the first in the tab order and that I want the form to look good
when opened, but that I want the information not to change it's formatting
when the user selects the field to edit it's contents. Right now the form
looks OK already, as long as the users do not select a date/time field to
change it's value. Once they do that, the previous (condense) format that
the control used is disregarded and the content gets enlarged beyond the
field's borders because Access wants to use a more verbose format instead.
And even worse: 13:00 changes into 1:00 PM, which makes it even less
intuitive to change it into the correct value... And then there is the
issue that it only happens with some date/time combinations, and not with
others, which makes it look even more like a bug on my part

It's this switch to Access' default formatting I want to prevent, and to let
Access use my specified formatting in all cases, no matter what is selected
on the form.

Kind regards,
Carl Colijn

 

[strive4peace]

Hi Carl,

<<< ... I want the information not to change it's formatting
when the user selects the field to edit it's contents ...<<<

not possible, sorry


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Carl Colijn]

In strive4peace" <"strive4peace2006 at yahoo dot com <"strive4peace2006 at
yahoo dot com"> typed:

Hi Carl,

<<< ... I want the information not to change it's formatting
when the user selects the field to edit it's contents ...<<<

not possible, sorry

Hi Crystal,

I already suspected that much

Anyway, thanks for your effort!
Have a nice weekend,
Carl

 

[John Spencer]

Pardon me,

The only way that I can think of to handle this is to use two controls.
First control is the one you have now.
The second control is an unbound control that is the same size as the first
control and is placed underneath the first control.
When the first control gets the focus, use code to set the focus to the
second control.

The second control is where all the data entry takes place and has its value
set in the on current event of the form to be equal the format you wish to
use. When the user exits the second control, you will have to check the
value entered into the control to see if it is a valid date and time and if
so update the first control if the first control has a value different from
what was in the second control.

 

[strive4peace]

clever solution, John

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*