\$100,000.00 Question.

1. Senior Member
Join Date
Dec 1969
Posts
450

## \$100,000.00 Question.

Well, it&#039;s time for the daily Woodson asks a question for all those enlighteneed in the ways of development, God bless ya.<BR><BR>Today&#039;s topic: Access 2000 Using Sort by in a Select Statement<BR><BR>I have these 3 column fields: SalesImpacted, DueDate, OrigDate<BR><BR>I want to sort the recordset with priority to whether it is Sales Impacted. If it is and has a due date its due date whichever closest to the present date is first. Followed by the original date whichever is OLDEST.<BR><BR>Is SalesImpacted? If so, the sort first by closest due date and then by farthest orignaldate. <BR>Next:<BR>NotSales Impacted--&#062; sort by closest due date and then by farthest originaldate.<BR><BR><BR>Any help with this would help day be so much more satsfying...<BR><BR>Thanks! And good luck!

2. Senior Member
Join Date
Dec 1969
Posts
450

## Hold the phones!@!

ORDER BY Salesimpacted,[DueDate] DESC,[OrigDate] ASC and then what? <BR><BR>Where do I go my fellow brethren? Help a brother out...<BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: \$100,000.00 Question.

Order By SalesImpacted desc, datediff(&#039;d&#039;,DueDate, date()), datediff(&#039;d&#039;, OrigDate, date()) Desc<BR><BR>assuming salesImpacted is yes/no

4. Senior Member
Join Date
Dec 1969
Posts
635

## Well...

as far as my knowledge goes, you can&#039;t conditionnaly modify a sort by.<BR><BR>There are a few things you can do to work it out and the easiest way is to re-sort your recordset once you have the result since I understand that you don&#039;t know right up front the value of SalesImpacted.<BR><BR>Do you query normally, unsorted<BR><BR>once your ASP has the results, check the value of SalesImpacted (i assume it&#039;ll be the same for every record) and use the .sort property of the recordset to sort it properly.<BR><BR>Eniac<BR>

5. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## Use IIF()

ORDER BY SalesImpacted,<BR> IIF(SalesImpacted, DueDate, OrigDate) DESC,<BR> IIF(SalesImpacted, OrigDate, DueDate) ASC<BR><BR>That would sort by SalesImpacted first (True before False). If the sales were impacted, it would then sort by DueDate, otherwise sort by OrigDate. If the sales were impacted, it would then sort by OrigDate, otherwise sort by DueDate.<BR><BR>Is that what you want? Or, do you want to sort by a comparison of the DueDate vs. OrigDate?<BR><BR>Or what? I&#039;m confused.

6. Senior Member
Join Date
Dec 1969
Posts
450

## Goodness.,.. this is difficult.

And I thought I was on to something... <BR><BR>SalesImpacted is a yes/no column <BR><BR>if salesimpacted = yes and duedate = "a date" then<BR>duedate equalto or closet to Now() has priorities over originaldate which is sorted with oldest first. <BR><BR><BR>if salesimpacted = "no", then by closest due date and then oldest original date

7. Senior Member
Join Date
Dec 1969
Posts
450

## Duedate is optional .... Origdate and Salesimpact

clarification

8. Senior Member
Join Date
Dec 1969
Posts
450

## Thanks, worked like a charm!!!! c ya tomorrow.

nt

#### Posting Permissions

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