James,

I'd include a calculated sortfield in the SELECT clause and then order on that (off the top of my head - i.e. not tested):

SELECT *, IIf(Left(CRRT,1) = "B", Zip4, DP) as sortfield 
  From MasterList 
  Into Table ClientFile 
  Where Zip = "96700" 
  Order By MailStream ASC, Zip ASC, CRRT ASC, SortField ASC

Cheers,

Andrew


>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