Sorting Records

[YoungBuck]

OK here is the setup of my database

Table #1 Name = Lists

Fields:

ListName
ItemName
AmtNum
AmtType

Table #2 Name = Items

Fields:

ItemName
Category

Now what I want to do is Open a recordset with an SQL statement similar to this and have it sorted by Category

sSQL = "SELECT * FROM Lists WHERE ListName = '" & pListName & "' ORDER BY Items.Category ASC;"

However (obviously) this doesn't work, how can I get this recordset sorted by category using an SQL statement, any ideas?

[HeSaidJoe]

If you are sorting by you must include the field you are sorting on. As you have it you have not included the field catogory into your selection as you are only selecting from table1.
NOt sure on this but something to this effect

sSQL = "SELECT * FROM Lists,Category from items WHERE ListName = '" & pListName & "' ORDER BY Items.Category ASC;"

sSQL = "SELECT * FROM Lists , Items WHERE ListName = '" & pListName & "' And Lists.ItemName = Items.ItemName ORDER BY Items.Category ASC;"

[YoungBuck]

Thanks jahangir and HeSaidJoe works great now!