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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
jrb1senior developerCommented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
jrb1senior developerCommented:
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
jjones666Commented:
use
INSERT INTO my_ado(id,name,dt) values(1,100,format('22/apr/2007', "dd/mmm/yyyy"))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.