How to sort text field on charchters at specific position? 1

[khwaja]

I am using a text field to enter proposed opening date as mmm-yy. I need it in text form because I occasionally want to write TBA where opening dates are not available. I need to be able to sort this field so that I get the right date order followed by TBAs. For example I have following the list.

Nov-05
Aug-01
Tba
Oct-02
Jul-03
Tba
Nov-03
Aug-03
Sep-05
Jul-04
Dec-01
Tba

Ideally, it should sort based on year and then month. I wonder if there is any way I can achieve this?

 

[tlbroadbent]

Try this. This will cause "tba" to be sorted last. All dates will be sorted in date order.

Select OpeningDate
From Table
Order By
IIf(OpeningDate="tba", 1, 0),
IIf(OpeningDate="tba", 0, datevalue("01-" & OpeningDate)) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[khwaja]

Terry

Thank you for our help. I am getting a syntax error message which basically points to the begining of second IIf statement. Somehow, it does not like having full IIf formula in the begining. When I removed 0 as the second argument from the first statement, I got the dates but all 'tba' were showing an #error!. I am sure this is a minor thing. Would you like to review the formula? Would this still be a text field?

Cheers

 

[tlbroadbent]

Try enclosing OpeningDate in brackets in the IIF function. Other than that, I don't see why it isn't working.

Select OpeningDate
From Table
Order By
IIf([OpeningDate]="tba", 1, 0),
IIf([OpeningDate]="tba", 0, datevalue("01-" & [OpeningDate])) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[khwaja]

Terry

Sorry to be back with this but this did not work even after I re wrote the formula. I have a feeling that since you wrote this as SQL code and I am using it as straight formula in my query, there may be a difference in the systax. If it is not too much trouble, could you please review this as a normal firmula? I modified this formula as under to keep me going:

Opening: IIf([PropOpening] Like "*tba*","Tba",IIf([PropOpening] Not Like "*tba*",Format(DateValue("01-" & [PropOpening]),"Medium Date&quot))

This works fine as long as the full date starts with '1' but not when date is other than '1'. In such cases it shows an error in the cell. I am happy to stick to your solution if that works.

Regards

AK

 

[tlbroadbent]

Modify the formula as follows. Add IsDate to verify the value is a date.

Opening: IIf([PropOpening] Like "*tba*", "Tba", IIf(IsDate("01-" & [PropOpening]), "01-" & [PropOpening], "Unknown&quot)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[khwaja]

Thanks. This works but still does not recognise if there is a full date in the source column. Following excerpt will highlight the issue which shows the source column and the result I got. Formula shows 'Unknown' where either there is no date (which is right) or where a full date is shown.

PropOpening Opening
Mar-06 01-Mar-06
Mar-02 01-Mar-02
Tba Tba
Jun-03 01-Jun-03
Unknown
Nov-02 01-Nov-02
Jul-01 01-Jul-01
Feb-02 01-Feb-02
Tba Tba
1-Sep-03 Unknown
Tba Tba

Is there some way I can have the full date returned too?

Cheers

 

[tlbroadbent]

Sorry. You didn't provide any examples with a full date. You said that all the dates were mmm-yy format.

If you try to understand the query I provided and work on it yourself, you'll be a lot better off than having someone else provide every answer. ;-)

There! Got that off my chest. Here is a possible solution. I hope the syntax is corect. I should be |-I.

Opening: IIf([PropOpening] Like "*tba*", "Tba", IIf(IsDate([PropOpening]), [PropOpening], IIf(IsDate("01-" & [PropOpening]), "01-" & [PropOpening], "Unknown&quot))
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.