Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-04-10
9
Medium Priority
?
1,289 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:eylkrn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 67

Accepted Solution

by:
sirbounty earned 672 total points
ID: 18885602
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
 
LVL 12

Expert Comment

by:jkaios
ID: 18885765
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
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 664 total points
ID: 18885838
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18886639
Please don't cross post in the MS SQL Server Zone, this question is unrelated there
0
 

Author Comment

by:eylkrn
ID: 18887628
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
 
LVL 25

Expert Comment

by:jrb1
ID: 18889252
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
 
LVL 1

Assisted Solution

by:jjones666
jjones666 earned 664 total points
ID: 20465740
use
INSERT INTO my_ado(id,name,dt) values(1,100,format('22/apr/2007', "dd/mmm/yyyy"))
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question