DSN - no records returned from query

[socket]

Hi
I have a system DSN that points to an access database. Everything works fine on my dev PC and in MS Access itself but on the website there are a couple of queries that return no records. The sql gets used with different critria and some work but others don't. Does anyone have any ideas where to look. I've pasted in a working and non working one, there's so little difference and it works fine in Access and on the Dev PC to the same database.
Thanks
Steve

Here's a working SQL
SELECT Tblprducts.firstname & ' ' & Tblprducts.surname as Fullname, TblPrducts.ProductCode, TblPrducts.ProductName, TblPrducts.ProductPrice, TblPrducts.Inventory, TBLPrducts.id, TblPrducts.Mancode, TblPrducts.LongDesc, TblPrducts.ProductManufacturer, TblPrducts.RetailPrice, TblPrducts.Image, BibleInfo.Translation1 FROM BibleInfo RIGHT JOIN TblPrducts ON BibleInfo.ProductCode = TblPrducts.ProductCode WHERE (((TblPrducts.ManCode)='Books')) AND TblPrducts.ProductCategory = 'BIBLE REFERENCE' Order By -TBLPrducts.id

and one the doesn't
SELECT Tblprducts.firstname & ' ' & Tblprducts.surname as Fullname, TblPrducts.ProductCode, TblPrducts.ProductName, TblPrducts.ProductPrice, TblPrducts.Inventory, TBLPrducts.id, TblPrducts.Mancode, TblPrducts.LongDesc, TblPrducts.ProductManufacturer, TblPrducts.RetailPrice, TblPrducts.Image, BibleInfo.Translation1 FROM BibleInfo RIGHT JOIN TblPrducts ON BibleInfo.ProductCode = TblPrducts.ProductCode WHERE (((TblPrducts.ManCode)='Bibles')) AND TblPrducts.ProductCategory = 'BIBLE SOFTWARE' Order By -TBLPrducts.id

[RFH Reseller: Sol]

Are you sure you have data correctly stored in the tables as you are limiting on 2 different parameters in your query.

Also double check the spellings in the ProductCategory field as well as checking any padding since you are checking for an exact string.

I also noticed that you have '-TBLPrducts.id' at the end of the query, and believe this should be 'TBLPrducts.id'.

[socket]

Thanks for the reply, i'm sure the data is right but I'll check for padding and try using like instead of equals. The minus on -TBLPrducts.id should sort the data inversely. The strange thing is that I've setup a DSN on my XP machine with the same access database file and it works. I'll probably create a new ASP just with the SQL in and try and gradually cut it down till it works.

cheers

Steve

[Tanzy]

The minus on -TBLPrducts.id should sort the data inversely.

For sorting you should always use

ORDER BY column ASC

or

ORDER BY column DESC

[socket]

Thanks for your help

it works OK using LIKE instead of = and I have changed the sort to DESC. I'm not sure why it works on my XP machine but haven't got the enthusiasm to look further as it's now working.

best regards

STeve