a date bug?

[JethroUK©]

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

 

[Duane Hookom]

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

 

[JethroUK]

"....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

 

[JethroUK©]

"...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]

"...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.

 

[JethroUK©]

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

 

[Rick Brandt]

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.