Solved

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

Posted on 2007-04-10
9
1,278 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
9 Comments
 
LVL 67

Accepted Solution

by:
sirbounty earned 168 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 166 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 166 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now