Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003

    Red face Unanswered: SQL Date format to ACCESS

    I have a yyyy-mm-dd 00:00:00.000 format in MS SQL, using it as a linked table. In my form, I want to display as mm/dd/yyyy and be able to put in date order but under properties for the field,

    FORMAT mm/dd/yyyy or short date is not working.

    This is in a text or varchar, so that is why I am having some problems.

    What do I have to do?
    Last edited by databasemon; 09-21-10 at 16:45.

  2. #2
    Join Date
    Nov 2003
    Please close this as I have resolved this issue.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    convert the data type to date in the db.
    use the appropriate data type for each column. unless you need to stroe number as text stroe them as number, likewise with dates.
    don't take the soft option,do it right.

    so I'd recommend you change the datatype in your SQL server
    however if you want a kludge workaround try

    or even kludgier
    = mid$(mydatecolumn,6,2) & "/" & mid$(mydatecolumn,9.2) & "/" & left$(mydatecolumn,4)

    but go back and change the datatype to date and it will be easier (and correct) in the medium to long term
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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