order by price problem
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=13659
Printed Date: 30 March 2026 at 1:11pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: order by price problem
Posted By: rocketdawg
Subject: order by price problem
Date Posted: 05 February 2005 at 11:35am
|
When displaying a list of cd's and their prices, the list is displayed in this order - ie:
1.40
1.50
2.00
21.00
4.50
45.90
7.50
etc
So, how do you get it to display in actual order of price? 7.00 should come before 21.00
Access 2000
my select statement:
strSQL = "SELECT * FROM products WHERE type = ""cd"" ORDER BY productPrice"
I'm sure it must be something simple, I just can't seem to work this one out. Thank you for your help.
|
Replies:
Posted By: dpyers
Date Posted: 05 February 2005 at 11:50am
|
is product price a currency field or a text field? ------------- 
Lead me not into temptation... I know the short cut, follow me. |
Posted By: rocketdawg
Date Posted: 05 February 2005 at 12:03pm
|
productprice is a text field. I tried this just to see what would happen:
strSQL = "SELECT * FROM products WHERE type = ""cd"" ORDER BY (productPrice*100)"
and it worked! It now orders correctly by price, and, miraculously it leaves the decimal point in the right place in the display table.
You raise a question in my head though. Why does a math calculation work on a text field? I would think that it would have to be a numeric field to have math performed on it. Or would it be easier just to accept the fact that it does, and move on? I only ask to understand, and perhaps help someone else with the same problem.
|
Posted By: Gullanian
Date Posted: 06 February 2005 at 12:14am
| I think access is clever in datatypes and allows executions such as that, although it's better practise to make mathamatical operations in sql queries be run on numerical datatypes for database compatability if you ever choose to move. |
Posted By: michael
Date Posted: 06 February 2005 at 10:48am
|
actually it is ok to do math operations on text fields but you need to built in some error handling. Access automatically tries a cast on that field, but better practice is to make the field numeric. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |