Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    text to date format (2000 SR1)

    I have a field [TextDate] which is imported from a text file that contains mostly dates and some "N/A" values. I create a query with a calculated field NewDate: iif([TextDate]="N/A",#31/12/2999#,format([TextDate],"Short Date")) and set the format of the field to short date.
    This appears to work - However when I sort this field (ascending) it lists the dates in alphabetical order, eg 01/07/2002 comes before 23/03/2002. ie it sorts on the first characters.
    How can I get this to work so I can sort in true date order.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: text to date format (2000 SR1)

    Format returns a string value, so NewDate is interpreted as a string field (the values are problably left-aligned now - always a telltale that something is text).

    Try:

    NewDate: IIf([TextDate]="N/A",#31/12/2999#,CDate([TextDate]))

    or

    NewDate: IIf([TextDate]="N/A",Null,CDate([TextDate]))

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: text to date format (2000 SR1)

    NewDate is not a date field, it is a text field, so it sorts from left to right. You need to convert it to a date, something like this:
    NewDate: CDate(IIF(textdate="N/A",#31/12/2999#,textdate))
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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