date format

[Fadi]

hi all
i have a problem
there is a field on my table with the following formats:
data type : date/time
format : dd/mm/yyyy
(also tried to put short date)
input mask : 99/99/9999
(i also tried to left it empty)
and when i try to insert
07/03/2004 (07 March 2004)
its changed automatically to 03/07/2004 (03 July 2004)
what i mean is take it as mm/dd/yyyy but i want it to be dd/mm/yyyy

did i miss anything ??!!!
thanks for any help

NOTE: short date format in regional and language options is dd/mm/yyyy

Fadi

 

[Allen Browne]

Hi Fadi

I work in a d/m/y country, but have never seen the symptoms you describe. My
experience with dates in Access is described here:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The final note of your post suggests that the Regional Settings in Windows
Control Panel has Short Date defined as dd/mm/yyyy. That's correct.

Open the table in design view, and delete the values for the Format and
Input Mask properties. This tells Access to show the date as defined in the
Windows Control Panel. Save. Switch to data view. In the first row, enter:
7/3/2004
It should not spin this date around.
If it does, open the Immediate Window (Ctrl+G) and enter:
? CDbl(DLookup("MyDate", "MyTable"))
substituting your field name for MyDate, and your table name for MyTable.
You should see:
38053
If so, Access is storing the date correctly, and the problem is with the
display. If not, post a reply with the answer you get.

As explained in the article above, Access will spin the dates around if the
entry cannot be accepted as it is, such as 7/13/2004.

 

[Fadi]

Thanks Allen and Joseph for reply,
Allen,
i've did what you tolled me to do
7/3/2004 changed to 03/07/2004
Immediate Window gives me the following results:
1. Msgbox CDbl(DLookup("MyDate", "MyTable"))
38171
2. MsgBox month(DLookup("MyDate", "MyTbl"))
7
and when i insert (7/13/2004) changed to (13/07/2004) automatically.
and its gives me (38181) using Immediate Window for Msgbox
CDbl(DLookup("MyDate", "MyTable"))
thanks in advance

Fadi

 

[Allen Browne]

Okay, that's as I expected. The date is actually stored as 3 July 2004, so
the date value in the table is not what you thought it was.

In the Immediate window (or VBA code), an literal date value will be
understood to be mm/dd/yyyy first. The article explains that, and how it
differs from the way dates are interpreted in the interface.

If you have your regional settings correct, and you enter
3/7/2004
in the interface, Access will store it as 38053.

 

[Fadi]

Thanks again Allen,
every thing thatyou said is right
but i dont want to enter it m/d/yyyy
becoase we are using dd/mm/yyyy date format here in Jordan
but i need other settings to stay as it is on English (united states) in
regional settings
and if any other user try to insert a date, he/she will make an error data
any advice
thanks and best regards

Fadi

 

[Fadi]

Thanks again Allen,
every thing that you said is right
but i dont want to enter it m/d/yyyy
because we are using dd/mm/yyyy date format here in Jordan
but i need other settings to stay as it is on English (united states) in
regional settings
and if any other user try to insert a date, he/she will make an error data
any advice
thanks and best regards

Fadi

 

[Fadi]

oh thanks god,
i think it is working right now
i changed the regional settings to Arabic (Jordan) then i changed it back to
English(United States) and re-customised the short date format to dd/mm/yyyy
and its worked.

thanks Allen for your support and being patient
and also thank you for your other tips on your website
they are really helpful

Fadi