Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    63

    Unanswered: Correct format for dates and times MySql Backend

    Hi,

    I have an access DB frontend that works with a MySql backend (to a certain extent).

    I am havving difficulties getting the and date fields to work using an insert into: I get an error message (I think it was overflow or something like that.

    I tried putting quotes around them and storing them as text but this had the same results.

    Can anyone please tell me how to get dates and times to work correctly with MySql?

    Thanks
    ChrisOfCatford

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you reformatting the dates to the style(s) that MySQL can accept?

  3. #3
    Join Date
    Sep 2004
    Posts
    63
    Hi Healdem,

    Thanks for your reply.

    I was using FormatDateTime(Date, etc) but none of those work.

    I am not entirely new to this but I have not really messed around with dates much. How do I format the date to read YYYYMMDD or whatever it is after?

    I will look more myself but any help would be appreciated.

    Thanks,
    ChrisOfCatford.

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You use the format command eg Format(MyDate, "YYYYMMDD") - see Help for the full syntax. Access likes its dates surrounded by the # symbol and in the American date format, I don't know how MySQL likes its dates
    Regards
    Justin

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Format(MyDate, "YYYYMMDD") should do the trick. Although Access may like to encapsulate dates with a # symbol I'm pretty certain that you do not need them on pass through queries.

    If however you are using linked tables then you may need to use an access format and the # before and after the date.

  6. #6
    Join Date
    Sep 2004
    Posts
    63
    OK thanks to you both I have the date issue sorted.

    Now all I need is to work out what MySql wants in a time field (suppose I should have put this in the original post ).

    So far I have tried

    HH:MMS
    HHMMSS
    HH MM SS
    and several other ways.
    None seem to work.

    Can anyone help me clear this up please?

    Thanks,
    ChrisOfCatford

  7. #7
    Join Date
    Sep 2004
    Posts
    63
    Hello all,

    I have worked it out. For anyone who has had similar problems I am posting it:

    I downgraded the ODBC driver by 1 version and it now works with ANY date and time combination.

    Who'd have thought it was not an Access problem at all eh?

    Bye 4 now,
    ChrisOfCatford

Posting Permissions

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