Order

1. Senior Member
Join Date
Dec 1969
Posts
2,880

## Order

Thansk guys for the previous post. My next query is that my code cuurently orders the data from the lowest cost to the highest which works fine, however how do I order the data (from lowest to highest) after a percentage has been added? <BR><BR>Existing Code:<BR>Set RSFind = Conn.Execute("Select * " _<BR> & "From " & DBTableName & " Where " _<BR> & "UserID = &#039;" & UserID & "&#039; And " _<BR> & "Region = &#039;" & RegionTitle & "&#039;" _<BR> & "Order By Cost1")<BR><BR>PercentageTotal = (RSFind("Cost1") * RSFind("Percent1") + RSFind("Cost1")

2. Senior Member
Join Date
Dec 1969
Posts
1,407

## RE: Order

i don&#039;t know if you can do this or not, but you may want to try this:<BR><BR>& "Order By (Cost1 + (Percent1 * Cost1))")<BR><BR>my gut feeling is this won&#039;t work.

3. Senior Member
Join Date
Dec 1969
Posts
1,407

## RE: Order

i&#039;m no sql expert, but can&#039;t you create a calculated field, something like:<BR><BR>SELECT Cost1, Percent1,...,Cost1+(Percent1*Cost1) AS PercentageTotal FROM whatever WHERE whatever ORDER BY PercentageTotal<BR><BR>again, it&#039;s just a strectch.....probably wrong.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Yes, if not Access...

Access doesn&#039;t allow you to use "AS" field names in an ORDER BY (or a GROUP BY for that matter).<BR><BR>But you *can* use the field *NUMBER* with Access:<BR><BR> SELECT Cost1, Percent1, Cost1+(Percent1*Cost1) AS PercentageTotal <BR> FROM whatever <BR> WHERE whatever <BR> ORDER BY 3<BR><BR>Because "PercentageTotal" is the 3rd field of the SELECT.<BR><BR>By the way, I think Trippy said she wanted highest first, so that would be<BR> ORDER BY 3 DESC<BR><BR>Finally: If you are really storing PERCENT1 as a percentage number (e.g, 10 means 10%), then you need to use<BR> Cost1+(Percent1*Cost1/100.0) AS PercentageTotal <BR>don&#039;t you?<BR><BR><BR>

#### Posting Permissions

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