Results 1 to 10 of 10

Thread: Date in Access

  1. #1
    Join Date
    Oct 2003
    Posts
    30

    Unanswered: Date in Access

    Hello,

    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) & "'," _
    & "'Caisse')"

    Thank you very Much

    Mark

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    30
    >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.

    With RSone

    .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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do i have any idea how to solve the problem?

    yes

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    30
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Posts
    30
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --

    SELECT FORMAT(datefld,'dd/mm/yyy')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2003
    Posts
    30
    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.

  10. #10
    Join Date
    Oct 2003
    Posts
    30

    IT WORKS!!!

    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.

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •