Different result for query through ODBC to FoxPro

Different result for query through ODBC to FoxPro

Post by Alex912 » Sun, 11 Nov 2001 08:09:20


I am making query in Access 2000 to FoxPro 2.5 dbf through ODBC and each
time getting
different result.

First query - calls 1:

SELECT DISTINCTROW arsales.invonum, arsales.invoext, arsales.ponum,
arsales.soldto, arsales.billto, arsales.company, arsales.clerk,
arsales.dateord, arsales.dateship, arsales.taxrate, arsales.shipping,
arsales.total, arsales.mkcode, arsales.cod,
(Left([arsales]![mkcode],3)='LLL' Or Left([arsales]![mkcode],3)='CCC' Or
Left([arsales]![mkcode],4)='BBBB') AS Expr1

FROM arsales

WHERE (((arsales.dateship)>=DateValue('05/01/01') And
(arsales.dateship)<=DateValue('08/31/01')) AND
(((Left([arsales]![mkcode],3)='LLL' Or Left([arsales]![mkcode],3)='CCC' Or

ORDER BY arsales.mkcode DESC;

And second one:

SELECT First([calls 1].mkcode) AS FirstOfmkcode, Count(*) AS [Count Of
arsales], Left([arsales]![mkcode],3) AS Expr1

FROM [calls 1]

GROUP BY Left([arsales]![mkcode],3)

ORDER BY Left([arsales]![mkcode],3) DESC;

And result that I got could be:






Always 1 moving to random line!

I checked it through FoxPro and right answer is:


 Thank you for good explanation



1. Different query results between ODBC and Enterprise Manager Query Analyser

I am using SLQServer 7.0 and the SQL-Server ODBC driver version
and I have a strange problem. My application generates its own SQL
statements and on the query

select * from phone
where phone_zipcode<'1000' or (phone_zipcode='1000' and
order by phone_zipcode desc,phone_sequence desc

no rows are returned. They are present however, and when I enter the same
query manually in the Query Analyser of the Enterprise Manager the correct
rows are returned. By the way, all expected rows have the same phone_zipcode
of '1000' since this is the smallest zipcode in the table and approximately
7000 rows have this zipcode. If I change '1000' to for example '1012' which
would produce different phone_zipcode values the ODBC driver does return the

Does anyone know why the ODBC driver returns different results than the
Enterprise Manager or how I could solve this problem?

Any help would be appreciated.

Thanks in advance,

Silvio Bierman

2. Oracle Database OCI / SQL Net / Minimum DLL's Required

3. Different Query results in Sql *Plus and Oracle ODBC 32Bit Test

4. question

5. ODBC Drivers

6. Query behaves different from ODBC 3.51 to ODBC 4

7. refresh say

8. Change query (ODBC driver), change query result

9. Different result between a query and a stored proc

10. Different results of same query in access 97 and SQL server 7.0

11. Different results in Query Analyzer(7.0) and ISQLW(6.5)

12. Different results when querying VIEW from QA and code