Totally puzzled by query incorrectly summing values

[Teri Welch]

Hello,

In Access 2000 SP3, we have a query built over a linked table (Foxpro DBF)
of order detail records. The query has a WHERE clause to filter the records
and we are calculating the sum() of the EXTPRICE field for the resulting
recordset. But the query is not calculating the sum correctly, in fact if we
run and rerun the query several times, we get several different values
(despite no changes being made to the underlying data or the query
definition) The COUNT() function reveals that the record count is
fluctuating too, albeit slightly, each time the query is run. After
experimenting a little, it seems the problem is somehow connected with one
selection criteria (shown in bold below) intended to omit all records whose
ITEM begins with "SHIP" while including everything else (even NULL items).
We dont underdstand why but when that criteria is removed the query runs
correctly. Can anyone help us understand what's happening and advise us how
to resolve this strange problem. Thank you.

Teri

SELECT A.* FROM ORDDET AS A
WHERE (((A.orderdate) BETWEEN #4/1/2007# And #4/30/2007#)
AND ((A.ordertype)<>'T' Or (A.ordertype) Is Null)
AND ((Mid$(Trim$([item]) & '',1,4))<>'SHIP' Or (Mid$(Trim$([item]) &
'',1,4)) Is Null));

 

[Allen Browne]

Try this:
SELECT A.*
FROM ORDDET AS A
WHERE (A.orderdate BETWEEN #4/1/2007# And #4/30/2007#)
AND ((A.ordertype <> 'T') Or (A.ordertype Is Null))
AND ((A.[Item] Like 'SHIP*') Or (A.[Item] Is Null));

I think that's what you want, and JET will be able to execute it without
having to call the VBA functions for Mid() and Trim(). You need Trim() only
if there are leading spaces (generally not good data.)

Using Trim$() forces the data type to string, which would fail if item were
null, so testing for Null would then be superfluous. Since you append an
empty string, the Mid$() would not fail, but again this forces it to a
string type, so it cannot be Null.

So, while the query results should not be inconsistent, there's too much
room for misinterpretation there.

I've assumed the ordertype field is Text, not yes/no which - from ancient
memory - would be represented as .T. in Fox, but True in Access.

If the count still fluctuates, it might be worth repairing the indexes in
Fox.

 

[Chris2]

Hello,

In Access 2000 SP3, we have a query built over a linked table (Foxpro DBF)
of order detail records. The query has a WHERE clause to filter the records
and we are calculating the sum() of the EXTPRICE field for the resulting
recordset. But the query is not calculating the sum correctly, in fact if we
run and rerun the query several times, we get several different values
(despite no changes being made to the underlying data or the query
definition) The COUNT() function reveals that the record count is
fluctuating too, albeit slightly, each time the query is run. After
experimenting a little, it seems the problem is somehow connected with one
selection criteria (shown in bold below) intended to omit all records whose
ITEM begins with "SHIP" while including everything else (even NULL items).
We dont underdstand why but when that criteria is removed the query runs
correctly. Can anyone help us understand what's happening and advise us how
to resolve this strange problem. Thank you.

Teri

SELECT A.* FROM ORDDET AS A
WHERE (((A.orderdate) BETWEEN #4/1/2007# And #4/30/2007#)
AND ((A.ordertype)<>'T' Or (A.ordertype) Is Null)
AND ((Mid$(Trim$([item]) & '',1,4))<>'SHIP' Or (Mid$(Trim$([item]) &
'',1,4)) Is Null));

Teri Welch,

Be aware that bold-face will not appear in most newsgroup readers (like mine).

Be aware that the double-quote mark is the standard string delimiter in MS Access.

Your query, straightened up just a bit.

SELECT A.*
FROM ORDDET AS A
WHERE (((A.orderdate) BETWEEN #4/1/2007# And #4/30/2007#)
AND ((A.ordertype)<>'T'
OR (A.ordertype) Is Null)
AND ((Mid$(Trim$([item]) & '',1,4))<>'SHIP'
OR (Mid$(Trim$([item]) & '',1,4)) Is Null));

The second line of the WHERE clause:

AND ((A.ordertype)<>'T' Or (A.ordertype) Is Null)

Should probably be:

AND ((A.ordertype <> "T") OR (A.ordertype Is Null))

Or at least it appears that way.


Also, as for:

OR (Mid$(Trim$([item]) & '',1,4)) Is Null));

If Trim$([item]) is NULL, the output of this will be a zero-length string, and so it never
be NULL.

(Null & "") results in zero-length string.

Mid$ will, at a minimum, also be operating on a zero-length string. Mid returns a
zero-length string if the Start argument is greater than the number of characters in the
string. It returns NULL only when the String argument is a NULL (and it won't be in this
case).

Paste the following into the Immediate window of the Visual Basic Editor and execute it:

? IIf((Null & "") = "", "True", "False")


Your Current Issue:

Without knowing your table structure and having an exact copy of some sample data, it is
difficult to determine.

My best guess is that the error is in the data.

To move toward solving the problem, I recommend simplifying the data you are working on.

On a *new copy* of your database (in the Foxpro db).

Create a copy of your table, and load the first half of the data from your real table into
the new table copy.

In MS Access, create a new linked table to the new Foxpro db.

Point your query at the new linked table.

Execute the modified query.

If the errors still exist, halve the data again (in the Foxpro db).

If the errors disappear, switch to the previously excluded half and proceed from there.

Repeat.

Basically, the high-low game.

If you go all the way down to one record remaining, and the query is still returning
incorrect COUNT and SUM values, report back on what the values are in that row (and with
your table structure).


Sincerely,

Chris O.

 

[Gary Walter]

Hi Teri,

Have you checked your odbc settings?

I have seen posts describing the same behavior
that were solved changing "BackgroundFetch"
to "No."

{quote from old post}

i found the problem in the odbc settings for visula foxpro databases. the
standard value for "BackgroundFetch" was set to "YES". when i set it to "NO"
all the results where ok!

{end quote}

good luck,

gary