Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

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

Results 1 to 3 of 3
Share This Thread →
  1. #1
    volvagia720 is offline Registered User
    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
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    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

  3. #3
    volvagia720 is offline Registered User
    Join Date
    Nov 2012
    Posts
    2
    Rep Power
    0
    Thanks, I used CDate(Format(The rest of my code)) it worked perfectly.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Rss2html asp feed, remove publish time from date
    By antonyx in forum ASP Development
    Replies: 5
    Last Post: December 17th, 2011, 02: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, 05:45 PM
  3. Format Date time
    By squirrelj in forum ASP Development
    Replies: 2
    Last Post: October 30th, 2006, 05:29 AM
  4. Format Date Time
    By aish in forum .NET Development
    Replies: 0
    Last Post: April 24th, 2006, 01:52 AM
  5. what format date and time should i use
    By okl in forum ASP Development
    Replies: 17
    Last Post: March 17th, 2005, 11:21 PM

ASP Free Advertisers and Affiliates