Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: How can I change date format w/o changing Date/Time data type

    The date field(mm/dd/yy) in a table has Date/Time data type but when I customized the date(mm/yyyy) using Format function in a make-table query it changed the data type to text in a temporary table.

    SELECT Format([dateentered],"mm/yyyy") AS [Date], Count([dbo_custconcerns].[qcappleprob]) AS Apple INTO Apple
    FROM dbo_custconcerns
    GROUP BY Format([dateentered],"mm/yyyy"), (DatePart("yyyy",[dateentered])), (DatePart("m",[dateentered])), ([dbo_custconcerns].[qcappleprob])
    HAVING (((([dbo_custconcerns].[qcappleprob]))=-1))
    ORDER BY (DatePart("yyyy",[dateentered])), (DatePart("m",[dateentered]));

    When I opened the temp table and tried to change the data type to Date/Time in the date field it won’t save. It seems the change must be set in the query to be effective.

    Can anyone help?
    Thanks!!

  2. #2
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    You should not use the format function in your query. After you run your make table query, try changing the properties on the date field in your temp table to make the format change you want. The first property for the table could be set to mm/yyyy and then the date/time data would always be retained. The table format property controls the way date/time data is displayed and printed - the original value can be displayed many different ways, depending on your needs.

    If you wish to retain the format setting on your temp table, create a small VB routine that runs your make-table query and then applies the format to the temp table.
    Regards,
    Terry

Posting Permissions

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