Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    173

    Unanswered: query formatting

    I have a calculated field in a query that pulls the first two characters of a serial number. These two numbers represent a year date. This query is then linked to a report that lists these serial numbers from oldest to newest. The report doesn't recognize that these represent years as it lists them like:
    00
    01
    98
    99

    Is there a way to make them sort by:

    98
    99
    00
    01

    I'm thinking in the calculated field there is a format I can put in to tell it that it's a date field.

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: query formatting

    Originally posted by leeroy
    I have a calculated field in a query that pulls the first two characters of a serial number. These two numbers represent a year date. This query is then linked to a report that lists these serial numbers from oldest to newest. The report doesn't recognize that these represent years as it lists them like:
    00
    01
    98
    99

    Is there a way to make them sort by:

    98
    99
    00
    01

    I'm thinking in the calculated field there is a format I can put in to tell it that it's a date field.
    Stick this field into the query as a column:

    Qdate: iif(val(left([Sno],2))>50,"19","20") & cstr(left([Sno],2)))
    sort on this field
    but don't (check it) put it into the results table

    In the year 2050 the program will fail. Are you worried about that?

  3. #3
    Join Date
    Jun 2002
    Posts
    173
    that works perfectly. thanks for the help!

Posting Permissions

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