Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Text Field (2000)

    I have a field in a table that is a text field where the entries are Jan 2002, Feb 2002, Mar 2002 etc. The problem is, I want to sort on this field making Jan 2002 the first and subsequent months in month order - not in alpha order. Is there anyway in a query to give each month a numeric equivalent so I can have Jan 2002 = 1 and so on. Something like if ([renewalmth]) = "Jan 2002" then 1 etc. I don't know how to do this. I need specifics. Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 Posts

    Re: Text Field (2000)

    The easiest way is probably to convert the field to a date/time field. If you have no spelling errors, Access will convert the values correctly to 1/1/2002 etc. You can set the Format property of the converted field to mmm yyyy to make it display exactly the way it did before.

    If that is not feasible, you could create an auxiliary table:

    <table border=1><td>TextValue</td><td>SortValue</td><td align=right>Jan 2002</td><td align=right>1</td><td align=right>Feb 2002</td><td align=right>2</td><td>Mar 2002</td><td align=right>3</td><td>...</td><td align=right>...</td></table>
    Add the main table and the auxiliary table to a query, join them on the text field (outer join, all records from the main table), and use the SortValue field to set the sort order.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Frederick, Maryland, USA
    Thanked 2 Times in 2 Posts

    Re: Text Field (2000)

    As Hans said, you could do the following in a query, Format([MonthField],'mm yyyy') and then set this field to sort ascending or descending in the query.

    (It's been a while!)

Posting Permissions

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