inconsistant query results (cross posted)

[Tom Williams]

in a nutshell:
I'm inserting rows into a table that I then produce a report
from. When I run this from my pc, I get the correct rows
inserted (I've hand counted and compared rows to be sure).
When I run this (same database on a file server) on 4 other
pc's, I don't get the correct number of rows inserted. The
incorrect results on the other 4 pc's are consistent.

What could cause this? I've narrowed it down to this one
statement and only when the parameter pWhichRpt is NOT "D"
("D"=daily rpt, "Y"= year-to-date rpt). There must be a
windows or access setting which affects the comparison in
the WHERE clause. Do you have any ideas what I could look
at? Four of the pc's are running WinXP, one runs Win2000.
All are running Access97.

Thanks for any ideas
Tom

code snippet:

Public Sub CreateDailySumRows(PTrgtDate As Date, pWhichRpt
As String)
Dim part1 As String, part2 As String, part3 As String, part4
As String

' Clean out old rows
part1 = "DELETE FROM [rpts-Summary]"
db.Execute (part1)

part1 = "INSERT INTO [rpts-Summary] (OrderID, RptDate,
Source, Sold) "
part2 = " SELECT O.OrderID, #" & Format(PTrgtDate,
"mm/dd/yyyy") & "#, O.Source, O.Sold "
part3 = " FROM [Orders Filled] as O "
part4 = "WHERE "
If pWhichRpt = "D" Then
part4 = part4 & " (not (O.SourceCat='C')) AND" '
no consignments on Daily report
End If

part4 = part4 & " (Format(O.Sold, 'Short Date') " &
IIf(UCase(pWhichRpt) = "D", " = ", " <= ")
part4 = part4 & " """ & Format(PTrgtDate, "Short Date") &
""" ) "

db.Execute (part1 & part2 & part3 & part4)

'****************
Debug.Print (part1 & vbCrLf & part2 & vbCrLf & part3 &
vbCrLf & part4)
results when pWhichRpt parameter = "Y":

INSERT INTO [rpts-Summary] (OrderID, RptDate, Source, Sold)
SELECT O.OrderID, #10/16/2003#, O.Source, O.Sold
FROM [Orders Filled] as O
WHERE (Format(O.Sold, 'Short Date') <= #10/16/2003# )

 

[[MVP] S. Clark]

Sorry to only ask the obvious, but do all machines share the same data, like
from a network?

 

[Tom Williams]

Yes the data is all the same. It is the same database on a shared network
folder that each pc is using.

I've figured out that the 1st part of the Where clause ( part4 = part4 & "
(Format(O.Sold, 'Short Date') " ) is causing a character comparison, not a date
comparison. When I corrected that, the query now returns the same results on my
PC and one other. Now, I don't understand why my pc was returning the correct
rows in the first place.

Perplexed, but feeling better
Tom

Sorry to only ask the obvious, but do all machines share the same data, like
from a network?

in a nutshell:
I'm inserting rows into a table that I then produce a report
from. When I run this from my pc, I get the correct rows
inserted (I've hand counted and compared rows to be sure).
When I run this (same database on a file server) on 4 other
pc's, I don't get the correct number of rows inserted. The
incorrect results on the other 4 pc's are consistent.

What could cause this? I've narrowed it down to this one
statement and only when the parameter pWhichRpt is NOT "D"
("D"=daily rpt, "Y"= year-to-date rpt). There must be a
windows or access setting which affects the comparison in
the WHERE clause. Do you have any ideas what I could look
at? Four of the pc's are running WinXP, one runs Win2000.
All are running Access97.

Thanks for any ideas
Tom

code snippet:

Public Sub CreateDailySumRows(PTrgtDate As Date, pWhichRpt
As String)
Dim part1 As String, part2 As String, part3 As String, part4
As String

' Clean out old rows
part1 = "DELETE FROM [rpts-Summary]"
db.Execute (part1)

part1 = "INSERT INTO [rpts-Summary] (OrderID, RptDate,
Source, Sold) "
part2 = " SELECT O.OrderID, #" & Format(PTrgtDate,
"mm/dd/yyyy") & "#, O.Source, O.Sold "
part3 = " FROM [Orders Filled] as O "
part4 = "WHERE "
If pWhichRpt = "D" Then
part4 = part4 & " (not (O.SourceCat='C')) AND" '
no consignments on Daily report
End If

part4 = part4 & " (Format(O.Sold, 'Short Date') " &
IIf(UCase(pWhichRpt) = "D", " = ", " <= ")
part4 = part4 & " """ & Format(PTrgtDate, "Short Date") &
""" ) "

db.Execute (part1 & part2 & part3 & part4)

'****************
Debug.Print (part1 & vbCrLf & part2 & vbCrLf & part3 &
vbCrLf & part4)
results when pWhichRpt parameter = "Y":

INSERT INTO [rpts-Summary] (OrderID, RptDate, Source, Sold)
SELECT O.OrderID, #10/16/2003#, O.Source, O.Sold
FROM [Orders Filled] as O
WHERE (Format(O.Sold, 'Short Date') <= #10/16/2003# )



--
E-mail correspondence to and from this address may be
subject to the
North Carolina Public Records Law and may be disclosed to
third parties.