locked
help with date query in access RRS feed

  • Question

  • hi

    i have field Tdate (text type) on my Table MyTbl

    i need query that sort by date.

    select * from MyTbl order by Tdate

    but because Tdate is Text i get wrong results

    how to fix it ?  is there any convert to date in access ?

    thanks in advance
    Sunday, February 13, 2011 11:24 AM

Answers

  • Another option is to test the text value in the query:

    select iif(IsDate(Tdate), CDate(Tdate), Null)
    from MyTbl

     


    -Tom. Microsoft Access MVP
    • Marked as answer by E_gold Sunday, February 13, 2011 7:16 PM
    Sunday, February 13, 2011 6:24 PM

All replies

  • hi,

    you need to convert the text into a date, this can be easily done in the query:

    SELECT  *
    FROM    MyTbl
    ORDER BY CDate(Tdate) ;

    This may fail, when your text field Tdate contains data which cannot be converted to a valid date.

    One approach would be a UDF (untested):

    Public Function TryCDate(AValue As Variant) As Variant
       On Local Error Resume Next
       ' Fallback value, MIN(Date/Time) = 1899-12-31
      TryCDate = CDate(0)
      ' or
      'TryCDate = Null
       TryCDate = CDate(AValue)
    
    End Function

    used as

    SELECT  *
    FROM    MyTbl
    ORDER BY TryCDate(Tdate) ;

    But you better should fix the data type, if it is intended to store only dates:

    Open your table in design view. Rename the Tdate column OldTDate. Add a new column named Tdate and set its data type to Date/Time. Save the table layout. Create a new update query. Run

    UPDATE  MyTbl
    SET     TDate = TryCDate(OldTdate) ;

    Check whether your date is correct. Then open the table in design view and delete the OldTDate column.

    Caveat: While this is the best approach for ensuring data integrity, it may break existing form or code logic. Thus you need to test your application.

    And: Make a backup before..


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Sunday, February 13, 2011 12:36 PM
  • Another option is to test the text value in the query:

    select iif(IsDate(Tdate), CDate(Tdate), Null)
    from MyTbl

     


    -Tom. Microsoft Access MVP
    • Marked as answer by E_gold Sunday, February 13, 2011 7:16 PM
    Sunday, February 13, 2011 6:24 PM