You got the right idea but wrong place to put IIF because foxpro doesn't allow expression like that in the ORDER BY clause
Select *, PADR(IIF(Left(CRRT,1) = "B", Zip4 , DP), 4) AS Sortorder ;
From MasterList ;
Where Zip = "96700"
Order By MailStream, Zip, CRRT, SortOrder ;
Into Table ClientFile
BTW, you don't need ASC in order by because it's default.
>Hi Folks,
>
>I have a table master table that we pull records from to give to clients (a mail list). We need the list sorted so that it qualifies for the discounts provided by the post office. In order to do this I need to sort it "conditionally". What I mean is, if the Carrier Route (CRRT) value starts with a "B" (PO Boxes), I need to sort on the Zip4 field (four digits after the zipcode). If Left(CRRT,1) <> "B" I need to sort on the Deliver Point (DP) field.
>
>So, I'm thinking something like:
>
>Select * From MasterList Into Table ClientFile Where Zip = "96700" Order By MailStream ASC, Zip ASC, CRRT ASC, IIf(Left(CRRT,1) = "B", Zip4 ASC, DP ASC
>
>
>My question is, first, if it is possible to use an IIF() statement in the ORDER clause of a SQL-Select command. Second, is it a good idea or is there a better way to do this.
>
>Thanks for all your help, on this thread and others.
>
>Aloha,
>
>James
--sb--