a date bug?

access seems to change the format of date fields just for editing e.g

20-Sept-05 (defined format) becomes 20/09/2005 13:07:00 (whilst editing)

it's not the first time i've noticed this strange behaviour (97, 2K & XP) -
at first i figured it was conflict between formats of the main table and the
form - but it actual happens in the main table itsself - it's painful to
edit - it's inconsistant (dosesn't happen in all tables, just some) anyone
else noticed this peculiar behaviour? and better still a cure - or is it
just a bug

JethroUK© wrote:
access seems to change the format of date fields just for editing e.g

20-Sept-05 (defined format) becomes 20/09/2005 13:07:00 (whilst
editing)

it's not the first time i've noticed this strange behaviour (97, 2K &
XP) - at first i figured it was conflict between formats of the main
table and the form - but it actual happens in the main table itsself
- it's painful to edit - it's inconsistant (dosesn't happen in all
tables, just some) anyone else noticed this peculiar behaviour? and
better still a cure - or is it just a bug

Formats going away when the field has focus is "working as designed". To get
what you want you have to use an InputMask.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

There are lots of calendar controls/forms that make date entry much more
convenient.

--
Duane Hookom
MS Access MVP


"Rick Brandt" wrote in message
m...
JethroUK© wrote:
access seems to change the format of date fields just for editing e.g

20-Sept-05 (defined format) becomes 20/09/2005 13:07:00 (whilst
editing)

it's not the first time i've noticed this strange behaviour (97, 2K &
XP) - at first i figured it was conflict between formats of the main
table and the form - but it actual happens in the main table itsself
- it's painful to edit - it's inconsistant (dosesn't happen in all
tables, just some) anyone else noticed this peculiar behaviour? and
better still a cure - or is it just a bug

Formats going away when the field has focus is "working as designed". To
get what you want you have to use an InputMask.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

"....Formats going away when the field has focus is "working as designed..."

It certainly isn't! - Like I said, it only occurs sometimes - It's not a
case of the 'format going away' - It's a case of access 'randomly'
reformatting the field during editing - A google search will find it's been
discussed 100's times without explanation (Just workarounds)

I can't use a control, it would turn a 1 second job into a 4 second job (1
weeks work into 1 months) - but thanx for the suggestion

Likewise with an input mask (ok to create but takes even longer to edit) -
To create an input mask every time you need a date/time field would render
the Access application 'totally unusable' (try using one on day-2-day basis)

Since there's no explaination for this quirky behaviour i'll assume it's
definately a Bug (A 10 year old Bug) - I'll post the explanation when i've
investigated it further


"Duane Hookom" wrote in message
...
There are lots of calendar controls/forms that make date entry much more
convenient.

--
Duane Hookom
MS Access MVP


"Rick Brandt" wrote in message
m...
JethroUK© wrote:
access seems to change the format of date fields just for editing e.g

20-Sept-05 (defined format) becomes 20/09/2005 13:07:00 (whilst
editing)

it's not the first time i've noticed this strange behaviour (97, 2K &
XP) - at first i figured it was conflict between formats of the main
table and the form - but it actual happens in the main table itsself
- it's painful to edit - it's inconsistant (dosesn't happen in all
tables, just some) anyone else noticed this peculiar behaviour? and
better still a cure - or is it just a bug

Formats going away when the field has focus is "working as designed". To
get what you want you have to use an InputMask.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

JethroUK wrote:
"....Formats going away when the field has focus is "working as
designed..."
It certainly isn't! - Like I said, it only occurs sometimes - It's
not a case of the 'format going away' - It's a case of access
'randomly' reformatting the field during editing - A google search
will find it's been discussed 100's times without explanation (Just
workarounds)

I should have also said "if the field has both a date that is not 12/30/1899 and
a time that is not midnight".
In that case it will always revert to a format that shows both while editing.
When doing so it uses the short date and time format as defined in Windows
regional settings.

Basically any time your format is hiding "pieces" of a DateTime value this has
to happen. You could use a format that only displayed the year, but still
contained all the other pieces. How would one change the month value in such a
field if the format didn't revert while editing?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

"...How would one change the month value in such a field if the format
didn't revert while editing?..."

I think it's safe to assume whichever format you 'choose' for display is
equally suitable for editing

I have a big database to finish and little time to spend on 'buggy
behaviour', it's for 3rd party use and thus extremely important it does
exactly what they expect it to do, otherwise i'll end up with a years
garbage instead of information so I have looked into this since and I have
found the explanation to be carved in stone for anyone else who hits this

Irespective of the output formatting, Access adopts the same input
formatting as when the cell was first 'input' - i say cell since it's
applies on cell by cell basis ( as opposed to whole record/whole field) -
particularly pertinent to me since most of the date fields were/are entered
programmatically (with option to manually amend) - i had to re-enter every
single date field (again programmatically), but now it's fine!


"Rick Brandt" wrote in message
m...
JethroUK wrote:
"....Formats going away when the field has focus is "working as
designed..."
It certainly isn't! - Like I said, it only occurs sometimes - It's
not a case of the 'format going away' - It's a case of access
'randomly' reformatting the field during editing - A google search
will find it's been discussed 100's times without explanation (Just
workarounds)

I should have also said "if the field has both a date that is not
12/30/1899 and
a time that is not midnight".
In that case it will always revert to a format that shows both while
editing.
When doing so it uses the short date and time format as defined in Windows
regional settings.

Basically any time your format is hiding "pieces" of a DateTime value this
has
to happen. You could use a format that only displayed the year, but still
contained all the other pieces.

How would one change the month value in such a
field if the format didn't revert while editing?

JethroUK© wrote:
"...How would one change the month value in such a field if the format
didn't revert while editing?..."

I think it's safe to assume whichever format you 'choose' for display
is equally suitable for editing

I have a big database to finish and little time to spend on 'buggy
behaviour', it's for 3rd party use and thus extremely important it
does exactly what they expect it to do, otherwise i'll end up with a
years garbage instead of information so I have looked into this since
and I have found the explanation to be carved in stone for anyone
else who hits this

Irespective of the output formatting, Access adopts the same input
formatting as when the cell was first 'input' - i say cell since it's
applies on cell by cell basis ( as opposed to whole record/whole
field) - particularly pertinent to me since most of the date fields
were/are entered programmatically (with option to manually amend) - i
had to re-enter every single date field (again programmatically), but
now it's fine!

My testing in Access 97 does not confirm what you're saying. I see MY format
until I click my cursor in the field and then I see the format as defined in
Regional Settings. If I change the Regional Settings I see that reflected when
I go back to the same table. Perhaps the newer versions behave differently.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

"Rick Brandt" wrote in message
t...
JethroUK© wrote:
"...How would one change the month value in such a field if the format
didn't revert while editing?..."

I think it's safe to assume whichever format you 'choose' for display
is equally suitable for editing

I have a big database to finish and little time to spend on 'buggy
behaviour', it's for 3rd party use and thus extremely important it
does exactly what they expect it to do, otherwise i'll end up with a
years garbage instead of information so I have looked into this since
and I have found the explanation to be carved in stone for anyone
else who hits this

Irespective of the output formatting, Access adopts the same input
formatting as when the cell was first 'input' - i say cell since it's
applies on cell by cell basis ( as opposed to whole record/whole
field) - particularly pertinent to me since most of the date fields
were/are entered programmatically (with option to manually amend) - i
had to re-enter every single date field (again programmatically), but
now it's fine!

My testing in Access 97 does not confirm what you're saying.

97 may not have same bug - try typing "1/6/06 11:00" in a date field -
deselect it - now click to edit

I see MY format
until I click my cursor in the field and then I see the format as defined
in
Regional Settings. If I change the Regional Settings I see that reflected
when
I go back to the same table. Perhaps the newer versions behave
differently.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

JethroUK© wrote:
"Rick Brandt" wrote in message
t...
My testing in Access 97 does not confirm what you're saying.

97 may not have same bug - try typing "1/6/06 11:00" in a date field -
deselect it - now click to edit

My field has a format of "mmm-yy". When I type the above into the field I see
"1/6/06 11:00" until I leave the field after which I see "Jan-06". When I click
back into the field to edit I see "01/06/2006 11:00:00 AM" which is the short
date and short time format as defined in Regional Settings.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com