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
If we were to introduce Visual FoxBase+, would we be able to work from the dotNet Prompt?
From Top 22 Developer Responses to defects in Software
2. "It’s not a bug, it’s a feature."
1. "I thought I fixed that."
All my FoxTalk and other articles are available on my
web site.
Unless specifically identified otherwise, anthing posted here is purely my opinion and may or may not reflect the policies or practices of Microsoft.