Date Problem

[Mighty]

I have two tables - So Master and Transaction History in Pervasive 2000. I have also imported these two tables into an Access DB.

When I run the following SQL query on the Pervasive Tables, I get no records returned:

SELECT DISTINCT "SO Master".CUSTPO_27, "SO Master".CUSTID_27, "Transaction History".ORDNUM_15 FROM "Transaction History" INNER JOIN "SO Master" ON "SO Master".ORDNUM_27 = LEFT("Transaction History".ORDNUM_15, 6) WHERE "Transaction History".TNXDTE_15='2001-11-07' ORDER BY "SO Master".CUSTID_27, "SO Master".CUSTPO_27

However, when I run the following query on the Access Tables I get 3 records returned:

SELECT DISTINCT "SO Master".CUSTPO_27, "SO Master".CUSTID_27, "Transaction History".ORDNUM_15 FROM "Transaction History" INNER JOIN "SO Master" ON "SO Master".ORDNUM_27 = LEFT("Transaction History".ORDNUM_15, 6) WHERE "Transaction History".TNXDTE_15=#07-NOV-01# ORDER BY "SO Master".CUSTID_27, "SO Master".CUSTPO_27

The only differences between the two queries is the date format and the access query returns the correct result. Is there something wrong in my Pervasive Date format. I have used this format in another Sql command. Mise Le Meas,

Mighty

 

[Vancouverite]

There are occasions when Pervasive's SQL engine has problems with INNER JOIN. Test this syntax:

SELECT DISTINCT "SO Master".CUSTPO_27, "SO Master".CUSTID_27, "Transaction History".ORDNUM_15 FROM "Transaction History", "SO Master"
WHERE "Transaction History".TNXDTE_15='2001-11-07'
AND "SO Master".ORDNUM_27 = LEFT("Transaction History".ORDNUM_15, 6)
ORDER BY "SO Master".CUSTID_27, "SO Master".CUSTPO_27