473,703 Members | 2,333 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date format frustration in forms

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
9 6369
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
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
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
"insomniux" <di*******@boss chaert.org> wrote in
news:11******** *************@j 33g2000cwa.goog legroups.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
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
NavigationButto ns = NotDefault
CloseButton = NotDefault
DividingLines = NotDefault
AllowDesignChan ges = NotDefault
DefaultView =0
ScrollBars =0
ViewsAllowed =1
TabularFamily =101
PictureAlignmen t =2
DatasheetGridli nesBehavior =3
GridY =10
Width =11623
DatasheetFontHe ight =10
ItemSuffix =90
Left =1950
Top =1830
Right =13230
Bottom =8655
DatasheetGridli nesColor =12632256
RecSrcDt = Begin
0x9b7c6559b4efe 240
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]"
DatasheetFontNa me ="Arial"

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

=====Form with correct behaviour====== =====
Begin Form
AllowDesignChan ges = NotDefault
ScrollBars =2
ViewsAllowed =1
TabularFamily =255
PictureAlignmen t =2
DatasheetGridli nesBehavior =3
GridY =10
Width =10601
DatasheetFontHe ight =10
ItemSuffix =19
Left =495
Top =2295
Right =11655
Bottom =9255
DatasheetGridli nesColor =12632256
RecSrcDt = Begin
0x2d5f21d733c4e 240
End
RecordSource ="SELECT * FROM letterAdmin ORDER BY [date] DESC ,
[direction], [destination]; "
OnOpen ="[Event Procedure]"
DatasheetFontNa me ="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
0xae022426a93a3 a408035beb71f91 6d31
End

May 12 '06 #6
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

"insomniux" <di*******@boss chaert.org> schreef in bericht news:11******** **************@ j33g2000cwa.goo glegroups.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
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

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(StartDa te)>12,StartDat e,DateSerial(Ye ar(startdate),D ay(startdate),M onth(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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
19846
by: koray | last post by:
hi everyone, in my form i have to take some date information in dd-mm-yy format. but i don't want user to use tabs while typing. for example s/he should simply type 280104 but 28/01/04 must appear. what can i do for that? should i use three input tags? but then, how can i make the cursor jump to the next field when typing in current field is done? or if i use one input tag, how can i keep '/' signs in the field fixed
10
5812
by: ARC | last post by:
Hello all, General question for back-end database that has numerous date fields where the database will be used in regions that put the month first, and regions that do not. Should I save a date format in the table design, such as: mm/dd/yyyy? What I've done for years is to store the date format in date fields, then on the forms, based on their region, I would set the date formats on form_load
3
421
by: Tony WONG | last post by:
The date format (control panel) of testing (VS2005) and production platform are yyyy/mm/dd. i run preview from testing (VS2005). The date format is yyyy/mm/dd however, when i put the code to production platform. The date format is dd/mm/yyyy what should i look to? Thanks a lot.
0
8758
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9121
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7867
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3123
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2450
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.