Date format frustration in forms

insomniux

Hi,
I am having a problem with formatting the default value of a date
field. It has been discussed earlier in many topics, but still I cannot
solve the problem. What's happening:
I have various forms which are based on an ODBC-linked tables. In one
of the forms, I have a control which shows the date of a date field in
my database (storage type=date). The default value for the control is
set to '=Date()', the format property is set to "dd-mmm-yyyy" (I'm
using Dutch MsAccess 2000). When I create a new record, and the day
number is below 13, the american date is shown (e.g. today (may-10) is
represented as 5-Oct-2006). When I remove the controlsource of this
control (so making it an unbound control) and open the form, it shows
the correct date. I've tried to format the date
'=Format(Date(),"dd-mmm-yyyy")', but the format command is completely
ignored.
The most frustrating is that on another form in the same database it
works correctly. Both database fields have the same internal storage
format and properties. I've tried to make a new control based on the
same controlsource, but this did not solve the problem.
Is there a way to avoid this problem (and possible erroneous database
entries) for good and all?
Thanks
Mike

May 10 '06 #1

RayPower

It may be the Windows regional setting is in American format. Change
the regional setting to Dutch & make sure the date format is correct in
the regional setting.

I have similar problem long time ago as we use Australian format. But
the IT guys always forgot to change the regional settings when
installing new PCs. I'm now having code to check the date format in the
startup form.

I hope that will help.

Cheers,
Ray

May 10 '06 #2

insomniux

Ray,
I'm afraid my regional setting is set to the local habits (date as
"dd-mmm-yyyy"). Even if this would be the cause, there is no
explanation for the fact that in one form the data is displayed correct
and in the other form it isn't.
Meanwhile I've tried to change the default value to
=day(Date()) &"-" & monthName(month(Date()),true) & "-"&year(Date())
But even forcefully setting the date to a string representing the
correct format is completely ignored and my 10th of may is represented
as 5th of October.
I am getting desperate!
Mike

RayPower wrote:

It may be the Windows regional setting is in American format. Change
the regional setting to Dutch & make sure the date format is correct in
the regional setting.

I have similar problem long time ago as we use Australian format. But
the IT guys always forgot to change the regional settings when
installing new PCs. I'm now having code to check the date format in the
startup form.

I hope that will help.

Cheers,
Ray

May 11 '06 #3

insomniux

Mmm..
Has this become an undiscussable issue (since it cannot be solved), or
is the obvious answer elsewhere, and is the question ignored because of
my ignorance?
I would greatly appreciate any help, since it IS a big problem for the
users of my database.
Thanks for any help
Mike

May 11 '06 #4

Lyle Fairfield

"insomniux" <di*******@bosschaert.org> wrote in
news:11*********************@j33g2000cwa.googlegro ups.com:

Mmm..
Has this become an undiscussable issue (since it cannot be solved), or
is the obvious answer elsewhere, and is the question ignored because of
my ignorance?
I would greatly appreciate any help, since it IS a big problem for the
users of my database.
Thanks for any help
Mike

I suggest that you save both the forms as text.
SaveAsText acForm, "NameOfForm", "NameofTextFile"

Then open with Notebook or WordPad or whatever, both files and examine them
carefully. Is there anything different about the definitions of the two
controls, the one that works correctly, and the other which does not.

If you can find nothing there, then what of the form definitions
themselves? Is there any difference there that might account for the
anomaly?

And if you find nothing there, then reexamine the fields to which the
controls are bound. Are they completely similar in every way?

Lastly I would (after making safety copies) load the forms back in from
text to ensure there is not some compilation gremlin screwing things up.
LoadFromText acForm, "NameOfForm", "NameofTextFile"

And if I still had no solution I would post the whole and complete text of
the "bad" control's form here together with the definition of its bound
field and ask, again, for guidance.

Actually, I would simply format all dates as yyyy-mm-dd according to ISO
standards. You can then be virtually certain that everything will work
correctly everywhere in the world.

--
Lyle Fairfield

May 11 '06 #5

insomniux

I can't find any relevant differences in the control definitions, nor
in the form definitions (both given below for both forms/controls).
After your suggestion I also reloaded the form from the text-export.
This has not changed the behaviour. The definition of the table-field
is date/time without any further format property specifications. Also
I've tried to set the format property of the control to yyyy-mmm-dd,
but today I still get 2006-dec-5.
Also in the source database there is no difference in table and field
definition.
These are the form and control properties:

=====Form with erroneous behaviour===========
[ps the recordsource is changed to a reasonable SQL statement by the
parent form]
Version =19
VersionRequired =19
Checksum =48840304
Begin Form
AutoResize = NotDefault
RecordSelectors = NotDefault
ControlBox = NotDefault
NavigationButtons = NotDefault
CloseButton = NotDefault
DividingLines = NotDefault
AllowDesignChanges = NotDefault
DefaultView =0
ScrollBars =0
ViewsAllowed =1
TabularFamily =101
PictureAlignment =2
DatasheetGridlinesBehavior =3
GridY =10
Width =11623
DatasheetFontHeight =10
ItemSuffix =90
Left =1950
Top =1830
Right =13230
Bottom =8655
DatasheetGridlinesColor =12632256
RecSrcDt = Begin
0x9b7c6559b4efe240
End
RecordSource ="SELECT d.*, t.trial FROM table_x AS d INNER JOIN
table_y AS t ON d.id_trialba"
"se=t.id WHERE 0=1; "
BeforeUpdate ="[Event Procedure]"
OnOpen ="[Event Procedure]"
DatasheetFontName ="Arial"

=====Control with erroneous behaviour===========
Begin TextBox
OverlapFlags =85
TextAlign =1
Left =1677
Top =633
Width =1589
ColumnOrder =2
Name ="screening_datum"
ControlSource ="screening_datum"
Format ="dd-mmm-yyyy"
StatusBarText ="screening date"
OnDblClick ="[Event Procedure]"
DefaultValue ="Date()"
GUID = Begin
0xaca5cc4b8d09694a9f34afaa8b2a4fd0
End

=====Form with correct behaviour===========
Begin Form
AllowDesignChanges = NotDefault
ScrollBars =2
ViewsAllowed =1
TabularFamily =255
PictureAlignment =2
DatasheetGridlinesBehavior =3
GridY =10
Width =10601
DatasheetFontHeight =10
ItemSuffix =19
Left =495
Top =2295
Right =11655
Bottom =9255
DatasheetGridlinesColor =12632256
RecSrcDt = Begin
0x2d5f21d733c4e240
End
RecordSource ="SELECT * FROM letterAdmin ORDER BY [date] DESC ,
[direction], [destination]; "
OnOpen ="[Event Procedure]"
DatasheetFontName ="Arial"

=====Control with correct behaviour===========
Begin TextBox
OverlapFlags =85
TextAlign =1
Left =2607
Width =1086
Height =450
TabIndex =3
Name ="date"
ControlSource ="date"
Format ="dd-mmm-yyyy"
DefaultValue ="Date()"
GUID = Begin
0xae022426a93a3a408035beb71f916d31
End

May 12 '06 #6

insomniux

Still the date issue is not solved.
When I set the date with the default value in the form, Access switches
month and day, when I set the value with a procedure while opening the
form the date is entered correctly.

Meanwhile I've found out that the problem is in fact not the form, but
the table. The behaviour is the same when I directly enter the date
into the table.

Now the most puzzling fact is the following. When I for instance enter
5/10/2006
and leave the field with the cursor, it is changed into 10/5/2006
When I now go back to the field, delete the 1 number and type it in
again and leave field again, it again switches the month and day, so
that without actually changing the value the date shows 5/10/2006
again.

It appears that whenever I enter a date in a date/time field, microsoft
assumes it needs to be converted irrespective of the value, as long as
the day is <= 12.

I'm using msaccess 9.0.6926 SP-3 (Dutch, no joke)
Does anyone know if this a know bug, or just one of those Microsoft
blessings one has to accept?
Thanks
Mike

May 17 '06 #7

Arno R

"insomniux" <di*******@bosschaert.org> schreef in bericht news:11**********************@j33g2000cwa.googlegr oups.com...

Still the date issue is not solved.

Q: Do you have this problem also with local tables?
If you can mail me the (stripped) problematic db I will have a look at it.

Arno R

May 17 '06 #8

insomniux

Yep, the problem is also present in native unlinked MS-Access tables.
Also in a database from scratch, with one table with one date/time
field.
Mike

May 17 '06 #9

cjb_kjb

I doubt I'm going to be much help to you but I have seen the problem
you describe. Unfortunately it was some time ago and I can't remember
the circumstances or how I overcame it - but I don't have it anymore.

The date format set in the control panel is Australian (d/mm/yyyy) -
but we didn't change this to fix the problem. I always use short date
format in table and form definitions (again d/m/yyyy).

I'm sure I fixed it either with setting a format in a definition or
with a format statement.

I've seen Access do some weird things at times which seem illogical and
inconsistent. Sometimes a reinstall can fix a problem.

Another thing you might try is to save a table that has a date field,
where you dont have the problem, with a different name. Change the
field definitions in that copy to those of a table where you do have
the problem. Dont delete the date field - just change it's name to
what you want it to be called (because you know that date field is
good). Write a query (or a program) to import data from the corrupt
table to the new table (checking that the dates are the right way
around before you store them) and then check if you have the problem in
the new table. If all is ok you could then discard the old table and
use the new in its place.

If the dates extracted from the corrupt table where the day is < 13
insist on coming out the wrong way around you could pull their
components apart and put then back together with dateserial - eg SELECT
BPA.BPAId, BPA.StartDate,
IIf(Day(StartDate)>12,StartDate,DateSerial(Year(st artdate),Day(startdate),Month(startdate))
) AS Xdate
FROM BPA. Using this example you'd store Xdate in the new table.

You wont know what the cause of the problem is but you might get around
it.

May 19 '06 #10