Using Pervasive SQL v8, this query ignores the WHERE clause. If I remove the INNER JOIN, the query works correctly. How do I do this query with the INNER JOIN?

Query = Select arlinh.documentnumber, arlinh.locationkey, arlinh.revenueacctkey, arlinh.shipdate, inloc.itemclasskey from arlinh INNER JOIN inloc ON (arlinh.itemkey = inloc.itemkey); WHERE ((arlinh.shipdate BETWEEN '2007-01-01' AND '2007-01-08') AND (arlinh.revenueacctkey BETWEEN '40000000' AND '4999Z999'))

With the INNER JOIN, all the records in the database are in the resulting recordset; while, without it, only the records defined by the WHERE clause are in the resulting recordset.

A couple of questions:
1. where are you running the query?
2. Is "Select arlinh.documentnumber, arlinh.locationkey, arlinh.revenueacctkey, arlinh.shipdate, inloc.itemclasskey from arlinh INNER JOIN inloc ON (arlinh.itemkey = inloc.itemkey); WHERE ((arlinh.shipdate BETWEEN '2007-01-01' AND '2007-01-08') AND (arlinh.revenueacctkey BETWEEN '40000000' AND '4999Z999'))" the exact query?

If number 2 is the correct query, what happens if you remove the semicolon (";") from the query so it looks like:
Select arlinh.documentnumber, arlinh.locationkey, arlinh.revenueacctkey, arlinh.shipdate, inloc.itemclasskey from arlinh INNER JOIN inloc ON (arlinh.itemkey = inloc.itemkey) WHERE ((arlinh.shipdate BETWEEN '2007-01-01' AND '2007-01-08') AND (arlinh.revenueacctkey BETWEEN '40000000' AND '4999Z999'))

1. I am running the the query in VB6-ADO
2. Yes, that is the exact query

I removed the semicolon and the query worked as it was suppossed to. I changed the WHERE dates and again, it worked correctly. I added some more fields and again, it worked correctly. Thanks.

No worries. The semicolon is a statement separator in PSQL.

Does that mean that I could (or should) have placed a semicolon at the end of the complete sql query?

You could but it's not required unless you've got multiple statements.

That's good to know. Thanks!