var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Date in Access
I am having a really big headache. I am trying use a form in VB to enter a Date and store it in Access.
The VB string is as : "13/08/2004" For 13th August
but Access stores it as 08/13/2004.
The field in Access is a Date/Time Field. If I use a Text/String Field in Access I get the same date as VB but I need the date field to be a Date format in Access and in the format dd/mm/yyyy and not mm/dd/yyyy.
Any Idea of how to do this.
I am inserting the date in Access using
strSQLInsertCaissier = "INSERT INTO Horaire (NomCollege, DateVente, HeureOuverture, HeureFermeture, Lieu," _
& "Nom, Poste) VALUES ('" & NomCollege & "'," _
13/08/2004 ----> & "'" & DateVente & "'," _
Gets changed & "'" & HeureOuverture & "'," _
to 08/13/2004 & "'" & HeureFermeture & "'," _
& "'" & Lieu & "'," _
& "'" & ArrayCaisse(IndexCaisse) & "'," _
Thank you very Much
no, access does not store it as 08/13/2004
access displays it as 08/13/2004
it is actually stored as two integers, one for the date and one for the time
TIP: to avoid any chance of any ambiguity, on your own machine today or anywhere else in the future, always type your date strings in ISO standard format, e.g. 2003-08-13
i am not aware of any database, access included, that has any difficulty with that format
TIP #2: if you want to see a date coming out of access in a particular format, use the FORMAT function
e.g. select FORMAT(datefld, 'dd/mm/yyyy')
>TIP #2: if you want to see a date coming out of access in a particular >format, use the FORMAT function
>e.g. select FORMAT(datefld, 'dd/mm/yyyy')
Ok Thanks for the tips. Let me explain you the problem I have maybe you can help me out.
Access Displays the date as you mentionned above 08/13/2004 instead of the format I want 13/08/2004.
I am doing a comparison in VB later on on that Date table.
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
' .Open "SELECT * FROM HoraireBAK WHERE DateVente >= #" & st & "# AND DateVente <= #" & se & "# ORDER BY DateVente", strcnx, , , adCmdText
and this SELECT statement does not behave properly because Access does not have the date in the format I want. Changing the whole code of the program would take weeks.
Do you have any idea how to solve this problem?
do i have any idea how to solve the problem?
enter your date strings in yyyy-mm-dd format, and access will then do the comparison on the datetime values properly
you say "Access does not have the date in the format" and i say again, a datetime value is stored internally in access as an integer
it is defined as a datetime field, right?
if so, you have nothing to worry about
yes it is defined as date/time field. I'll try to store it in the format you said and see what happens.
Thanks a lot.
Originally posted by mrhyman
I'll try to store it in the format you said and see what happens. NO NO NO!! DON'T DO THAT!!
all i was trying to get across to you is that a datetime field -- which is what you want to use -- is not stored in any particualr format
a datetime field is stored internally -- where you can't see it -- as an integer
every time you SELECT a datetime field, access converts it from its internal integer format to a character string which makes sense to us human beings
if you do not tell it which format you want via the FORMAT functin, then access will simply display it in the default format
just leave your column as datetime
Sorry but you completely lost me. I did not intend to change the datetime column. I just wanted to use the ISO Format you said.
But I still don't see how I will be able to perform a SQL SELECT on the date column and get my result as dd/mm/yyyy instead as mm/dd/yyyy.
Even using the ISO format you said Access will display it as MM/DD/YYYY. Right?
Therefore my problem is getting access to display it as dd/mm/yyyy and in date/time format. I think this is not possible.
you must separate in your mind two concepts
(1) specifying a date string whenever you give a query to access
(2) retrieving query results from access
in between (1) and (2), access stores the date in a secret internal format
if you specify your input strings in (1) in ISO format, access will never misunderstand you, will never confuse April 3rd and March 4th, and will hum along nicely
pause and take a deep breath
now that we have our dates stored in datetime fields properly, it is time to retrieve them again
run this query --
SELECT datefld from yourtable
since you did not specify a display format, which has nothing to do with how the date went in, you will see the date come back out in the default format
if you do not like the default format, you are free to ask for it in any format you want
specifically, you can ask for it like this --
ok it all makes sense to me now. Therefore the procedure I must change is the procedure that inserts the dates in my Datefield and then just add the Format(date,'dd/mm/yyyy') In my select statement.
Thanks a lot. I'll try that.
Thanks to everyone. All your advices did make it work. The moral of the story is use the ISO standard and nothing will go wrong.