Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Datetime to text (Access 2000)

    Hi,

    I have a linked table to sql. The table has a datetime hiredate field which I need to convert to text. So I created a query in access but I don't know how to create the function. Does anyone know how to do this?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Convert Datetime to text (Access 2000)

    The format$ function returns a string, so you could try something like:

    format$([hiredate],"mm/dd/yyyy hh:nn")

    In the quotes you can set whatever format you like
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Datetime to text (Access 2000)

    I tried using the following function based on your post:
    Format$([HireDate],"mmddyyyy")
    but, it get a "Data Type Mismatch error message".

    Does it have to do with the data table being linked to the Sql Server? However, I created a query off the table and created the function for the hire date field. Am I suppose to create a new field then place the function?
    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Convert Datetime to text (Access 2000)

    In the query you create a new field, give it any name you like followed by a colon, then the format$ function.
    eg. hiredatetxt: Format$([hiredatetime],"mmddyyyy").

    I attach a little Access97 db with a working example
    Attached Files Attached Files
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert Datetime to text (Access 2000)

    SQL Server date fields aren't the same as Access date fields. If you want to work with it as a date, you have to convert it in the SQL Server view, or you have to use something like CDate([HireDate]) to convert it to an Access date first.
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Convert Datetime to text (Access 2000)

    I nearly prefaced my previous answers with:
    "I don't know anything about SQL Server, but this works in Access."
    Clearly I should have.
    Regards
    John



  7. #7
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Datetime to text (Access 2000)

    Thanks!!!
    It worked perfectly.
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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