Search code examples
sqlms-accessjet

Access SQL database - ORDER BY

I'm using an MRP system for stocking inventory where I work. The interface it self isn't the best, so I have decided to open up the database file and do everything manually. I'm having some issues though. I'm trying to sort my database by using ORDER BY. I'm not getting the results I thought I would. It is showing them in this format:

1
10
100
101
101
11
110
111

etc

Instead of 

1
2
3
4
5

etc

This is my query

SELECT * 
    FROM tblStockItems
    Order By (`MasterPNo`)

I'm currently working in access, and then database is in the JET format. If you're wondering why I am using access instead of the MRP Interface, it is because later down the line I will be needing to re-organise the whole stock system, so a lot of fields will have their product numbers changed.

Thanks for reading

Solution

  • if possible, change the column type to number

    if not, a cast should do it:

    ORDER BY Val(MasterPNo)