how to use DD/MM/YYYY date format in access

Hello all,

I have a problem which I cant solve
I want to create a date col. with the format "DD/MM/YYYY" in access daabase. however, I can't do this. Does anyone have a solution to this? I've searched a lot in the web but coudnlt find a sol.

here is my code:

  Set conn = New ADODB.Connection
'  conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
                        & "SERVER=localhost;" _
                        & " DATABASE=test;" _
                        & "UID=root;PWD=; OPTION=3"
                       
conn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
                & "c:\temp" & "\" & "db1.mdb" & " ; DefaultDir=" & "c:\temp\" & ";"
               

  conn.Open

  'create table
  conn.Execute "DROP TABLE IF EXISTS my_ado"
  conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _
                                 & "txt text, dt date, tm time, ts timestamp)"
                                 
                                 

  'direct insert
  conn.Execute "INSERT INTO my_ado(id,name,dt) values(1,100,22/04/2007)" '  ERROR

conn.Close


Thanks in advance,
Eyal
eylkrnAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sirbountyConnect With a Mentor Commented:
Try:
 conn.Execute "INSERT INTO my_ado(id,name,dt) values(1,100,#04/22/2007#)"
or
 conn.Execute "INSERT INTO my_ado(id,name,dt) values(1,100,#22/04/2007#)"
0
 
jkaiosIT DirectorCommented:
Or try using single quotes around the date as well (if DB version is Access 2003 with "SQL Server Compatible Syntax" (ANSI 92))

conn.Execute "INSERT INTO my_ado(id,name,dt) values(1,100,'04/22/2007')"
0
 
jrb1Connect With a Mentor Commented:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22149298.html

A date field in the database does not have a format.  When you work with access, it will display based on the locale of the machine the code is running on.

I'd recommend you format it like:

INSERT INTO my_ado(id,name,dt) values(1,100,'22/apr/2007')
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Anthony PerkinsCommented:
Please don't cross post in the MS SQL Server Zone, this question is unrelated there
0
 
eylkrnAuthor Commented:
Hi all,

If I put the ' or # around the date , how will the computer that I'm using DD/MM/YYYY?
for example in :
conn.Execute "INSERT INTO my_ado(id,name,dt) values(1,100,'03/02/2007')"
How does  the computer think it March while I want it to be February?
0
 
jrb1Commented:
It won't which is why I recommended using the 3-character abbreviation of month--it's obvious what it means.  Otherwise, code all of the inserts as mm/dd/yyyy '02/03/2007' and the value will still be displayed DD/MM/YYYY because of the settings on your machine.
0
 
jjones666Connect With a Mentor Commented:
use
INSERT INTO my_ado(id,name,dt) values(1,100,format('22/apr/2007', "dd/mmm/yyyy"))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.