#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0

    Remove time from Date with Format. Lost ability to filter by date


    Access 2010
    Windows XP
    I'm trying to remove the time from a date and time column and then sort the data by date. The data starts in the query named "Modified Data" under the column of "DateAndTime" and is moved into the query named "Ordered Data". My Query of "Ordered Data" is as follows:

    SELECT Format([DateAndTime],"d /mm/ yyyy") AS RecordDate, [Modified Data].[0] AS Tag1, [Modified Data].[1] AS Tag2, [Modified Data].[2] AS Tag3, [Modified Data].[3] AS Tag4
    FROM [Modified Data];


    When I right click a value "DateAndTime" Column I get an option called "Date Filters" which implies that the data is formatted as a date and, I can sort it as such. When i click on the "RecordDate" Collumn in "Ordered Data" I get an option called "Text Filters" Which implies that the formatting was changed when I changed querys. How can I get rid of the time in my first query while still retaining the fact that access sees this as a date and I can filter based off of that fact?

    Note: i have tried the following as my query file:

    SELECT Format([DateAndTime],"Short Date") AS RecordDate, [Modified Data].[0] AS Tag1, [Modified Data].[1] AS Tag2, [Modified Data].[2] AS Tag3, [Modified Data].[3] AS Tag4
    FROM [Modified Data];
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,621
    Rep Power
    650
    Format results in a string (text) value. Convert the value back to a date with CDate() function.

    Or use Int() function to pull just the date part.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    Thanks, I used CDate(Format(The rest of my code)) it worked perfectly.

Similar Threads

  1. Rss2html asp feed, remove publish time from date
    By antonyx in forum ASP Development
    Replies: 5
    Last Post: December 17th, 2011, 03:30 PM
  2. How do I remove time from the date?
    By mcarrier in forum Microsoft Access Help
    Replies: 4
    Last Post: November 17th, 2011, 06:45 PM
  3. Format Date time
    By squirrelj in forum ASP Development
    Replies: 2
    Last Post: October 30th, 2006, 06:29 AM
  4. Format Date Time
    By aish in forum .NET Development
    Replies: 0
    Last Post: April 24th, 2006, 02:52 AM
  5. what format date and time should i use
    By okl in forum ASP Development
    Replies: 17
    Last Post: March 18th, 2005, 12:21 AM

IMN logo majestic logo threadwatch logo seochat tools logo