By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Why would a DCount of FieldName in saved query return a number greater than number of rows in qry?

P: n/a
MLH
?dcount("[OwnerFName]","qryOwnrsDueITSwMissingAddr")
when run in the immediate window return a number greater
than the number of rows that display when the saved query
is run - opening in the database window?

I consistently see 2 rows in qryOwnrsDueITSwMissingAddr
opening with a dbl-clik. Yet

?dcount("[OwnerFName]","qryOwnrsDueITSwMissingAddr")
returns 3 and

?dcount("[VehicleJobID]","qryOwnrsDueITSwMissingAddr")
returns 4.
Jan 12 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
It's a little hard to guess the reason when we have no idea what is in
qryOwnrsDueITSwMissingAddr, whether parameters function calls, or de-duping
could cause different results every time it is run.

One possibility is that you have a corrupted index. Try:
Tools | Database Utilites | Compact/Repair

If that does not work post the SQL statement for the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.net> wrote in message
news:1h********************************@4ax.com...
?dcount("[OwnerFName]","qryOwnrsDueITSwMissingAddr")
when run in the immediate window return a number greater
than the number of rows that display when the saved query
is run - opening in the database window?

I consistently see 2 rows in qryOwnrsDueITSwMissingAddr
opening with a dbl-clik. Yet

?dcount("[OwnerFName]","qryOwnrsDueITSwMissingAddr")
returns 3 and

?dcount("[VehicleJobID]","qryOwnrsDueITSwMissingAddr")
returns 4.

Jan 12 '06 #2

P: n/a
MLH
On Thu, 12 Jan 2006 22:38:16 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
It's a little hard to guess the reason when we have no idea what is in
qryOwnrsDueITSwMissingAddr, whether parameters function calls, or de-duping
could cause different results every time it is run.

You are absolutely right about that. Here's the SQL:

SELECT tblOwners.OwnerFName, tblOwners.OwnerLName,
tblOwners.OwnerAddr, tblOwners.OwnerCity, tblOwners.OwnerState,
tblOwners.OwnerZip, tblOwners.Corporation,
MakeITS([OwnerFName],[OwnerLName],[OwnerAddr],[OwnerCity],[OwnerState],[OwnerZip],[Corporation],[tblVehicleJobs].[VehicleJobID])
AS BodyText, tblVehicleJobs.VehicleJobID
FROM (tblOwners RIGHT JOIN tblVehicleJobs ON tblOwners.OwnerID =
tblVehicleJobs.OwnerID) INNER JOIN tblCorrespondence ON
tblVehicleJobs.VehicleJobID = tblCorrespondence.VehicleJobID
WHERE (((tblOwners.OwnerAddr) Is Null) AND
((tblCorrespondence.OutDate) Is Null) AND
((tblCorrespondence.OutType)="17")) OR (((tblOwners.OwnerCity) Is
Null) AND ((tblCorrespondence.OutDate) Is Null) AND
((tblCorrespondence.OutType)="17")) OR (((tblOwners.OwnerState) Is
Null) AND ((tblCorrespondence.OutDate) Is Null) AND
((tblCorrespondence.OutType)="17")) OR (((tblOwners.OwnerZip) Is Null)
AND ((tblCorrespondence.OutDate) Is Null) AND
((tblCorrespondence.OutType)="17"));

Jan 12 '06 #3

P: n/a
Well, I cannot see any valid reason for DCount() getting that wrong.

The query is relatively simple. You could try dropping the function call to
MakeITS() and see if that makes any difference. (It will certainly affect
the performance of the query, but should not affect the record count.)

The WHERE clause is equivalent to:
WHERE (tblCorrespondence.OutDate Is Null)
AND (tblCorrespondence.OutType = "17")
AND ((tblOwners.OwnerAddr Is Null)
OR (tblOwners.OwnerCity Is Null)
OR (tblOwners.OwnerState Is Null)
OR (tblOwners.OwnerZip Is Null))

Presumably OutType is a Text type field (not a Number type field.)

There's an outer join, but that should be fine. There are no calcualted
fields in the WHERE clause, no de-dupe. Hard to see how to could get it
wrong.

You did try the compact/repair?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.net> wrote in message
news:sp********************************@4ax.com...
On Thu, 12 Jan 2006 22:38:16 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:
It's a little hard to guess the reason when we have no idea what is in
qryOwnrsDueITSwMissingAddr, whether parameters function calls, or
de-duping
could cause different results every time it is run.

You are absolutely right about that. Here's the SQL:

SELECT tblOwners.OwnerFName,
tblOwners.OwnerLName,
tblOwners.OwnerAddr,
tblOwners.OwnerCity,
tblOwners.OwnerState,
tblOwners.OwnerZip, tblOwners.Corporation,
MakeITS([OwnerFName],[OwnerLName],[OwnerAddr],
[OwnerCity],[OwnerState],[OwnerZip],[Corporation],
[tblVehicleJobs].[VehicleJobID]) AS BodyText,
tblVehicleJobs.VehicleJobID
FROM (tblOwners RIGHT JOIN tblVehicleJobs
ON tblOwners.OwnerID = tblVehicleJobs.OwnerID)
INNER JOIN tblCorrespondence
ON tblVehicleJobs.VehicleJobID = tblCorrespondence.VehicleJobID
WHERE ((tblOwners.OwnerAddr Is Null)
AND (tblCorrespondence.OutDate Is Null)
AND (tblCorrespondence.OutType ="17"))
OR ((tblOwners.OwnerCity Is Null)
AND (tblCorrespondence.OutDate Is Null)
AND (tblCorrespondence.OutType ="17"))
OR ((tblOwners.OwnerState Is Null)
AND (tblCorrespondence.OutDate Is Null)
AND (tblCorrespondence.OutType = "17"))
OR ((tblOwners.OwnerZip Is Null)
AND (tblCorrespondence.OutDate Is Null)
AND (tblCorrespondence.OutType = "17"));
Jan 12 '06 #4

P: n/a
What is the code for MakeITS?

Jan 12 '06 #5

P: n/a
MLH
On 12 Jan 2006 09:33:05 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:
What is the code for MakeITS?

Function MakeITS(OwnerFName As Variant, OwnerLName As Variant,
RecipAddr As Variant, OwnerCity As Variant, _
OwnerState As Variant, OwnerZip As Variant, Corporation As Variant,
VehicleJobID As Variant) As String
'************************************************* *********************
' This function is called by qryITSLetterList, which furnishes all the
' arguments to this function.
'************************************************* *********************
100 On Error GoTo MakeITS_Err

110 Dim TheOwner As String, TheName As String, TheAddr As String,
TheCSZ As String, ThePhone As String
120 Dim TheCity As String, TheState As String, TheZip As String,
RecipName As String, RecipCSZ As String
130 TheOwner = DLookup("[TheOwnerFName]", "tblAdmin") & " " &
DLookup("[TheOwnerLName]", "tblAdmin")
140 TheName = DLookup("[Thempany]", "tblAdmin")
150 TheAddr = DLookup("[TheAddr]", "tblAdmin")
160 TheCity = DLookup("[TheCity]", "tblAdmin")
170 TheState = DLookup("[TheState]", "tblAdmin")
180 TheZip = DLookup("[TheZip]", "tblAdmin")
190 ThePhone = Format$(DLookup("[ThePhn]", "tblAdmin"), "(@@@)
@@@-@@@@")
200 TheCSZ = TheCity & ", " & TheState & " " & TheZip
210 If Corporation = False Then RecipName = Nz(OwnerFName) & " " &
Nz(OwnerLName) Else RecipName = Nz(OwnerFName)
220 If RecipName = "" Or RecipName = " " Or IsNull(RecipName) Then
RecipName = "Owner Name Unknown"
230 RecipCSZ = Nz(OwnerCity) & ", " & Nz(OwnerState) & " " &
Nz(OwnerZip)
240 If RecipCSZ = ", " Then RecipCSZ = "Owner City/State/Zip
Unknown"

' NOTICE OF INTENT TO SELL A VEHICLE TO SATISFY
STORAGE AND/OR MECHANICíS LIEN FORM LETTER

250 MyString = "Date: " & Format$(Now, "Long Date") & CRLF & CRLF

260 MyString = MyString & "To:" & CRLF & RecipName & CRLF &
Nz(RecipAddr, "Owner Address Unknown") & CRLF & Nz(RecipCSZ) & CRLF &
CRLF

270 MyString = MyString & "From:" & CRLF & TheOwner & CRLF & TheName &
CRLF & TheAddr & CRLF & TheCSZ & CRLF & ThePhone & CRLF & CRLF

280 MyString = MyString & "The person or firm shown in Section F of
the enclosed NOTICE OF INTENT TO SELL A VEHICLE TO SATISFY STORAGE
AND/"
290 MyString = MyString & "OR MECHANICS LIEN form intends to sell the
vehicle described on the form to satisfy a mechanicís and/"
300 MyString = MyString & "or storage lien. The amount of the lien,
the services for which the lien is claimed, and the date the lien
became "
310 MyString = MyString & "due are indicated in Section C and D of the
form." & CRLF & CRLF

320 MyString = MyString & "If you feel the lien described in Section C
of the enclosed form is invalid, or you wish to question the validity
"
330 MyString = MyString & "of the lien, you have the right to a
judicial hearing to determine the validity of the lien. To question
the valid"
340 MyString = MyString & "ity of the lien, you must notify the lienor
by completing the request for judicial hearing that appears at the
bottom "
350 MyString = MyString & "of this page and you must return this
letter to the lienor at the address indicated in Section F of the
enclosed NOTICE "
360 MyString = MyString & "OF INTENT TO SELL A VEHICLE TO SATISFY
STORAGE AND/OR MECHANICí LIEN form by registered mail or certified "
370 MyString = MyString & "mail, return receipt requested, within ten
(10) days." & CRLF & CRLF

380 MyString = MyString & "Your Failure to return this letter with the
Request For A Judicial Hearing properly completed to the lienor within
"
390 MyString = MyString & "ten (10) days from the date you received
this letter will be considered a waiver of your rights to a hearing
prior "
400 MyString = MyString & "to the sale of this vehicle and the lienor
may enforce the lien by selling the vehicle. If you send the Request
for "
410 MyString = MyString & "a Judicial Hearing to the lienor within the
10 day time period, it will be the responsibility of the lienor to
initiate "
420 MyString = MyString & "the proper action in a court of competent
jurisdiction." & CRLF & CRLF

430 MyString = MyString & "Thank you for your prompt attention to this
matter." & CRLF & CRLF & CRLF
440 CurrentVehicleJobID = VehicleJobID
450 Dim ccParties As String, MyDB As Database, MyQdef As QueryDef,
MyRecSet As Recordset
452 ccParties = "cc:" & CRLF
460 Set MyDB = CurrentDb()
470 Set MyQdef = MyDB.CreateQueryDef("", "Select * FROM
qryAuthsOwnersAddnlOwnersLienholders4OneCar")
480 With MyQdef
490 Set MyRecSet = .OpenRecordset(dbOpenSnapshot)
500 MyRecSet.MoveFirst
510 With MyRecSet
520 Do Until MyRecSet.EOF
522 If !OLAX <> "O" Then
530 ccParties = ccParties & !Item
532 End If
540 .MoveNext
550 ccParties = ccParties & CRLF
560 Loop
570 End With
580 End With
590 MyString = MyString & ccParties
600 MakeITS = MyString

MakeITS_Exit:
Exit Function

MakeITS_Err:
Dim r As String, z As String, Message3 As String
r = "The following unexpected error occurred in Function
MakeITS(), line #" & CStr(Erl) & " when called from qryITSLetterList:"
z = CRLF & CRLF & str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & z
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume MakeITS_Exit

End Function
Jan 12 '06 #6

P: n/a
So what are we left with?

470 Set MyQdef = MyDB.CreateQueryDef("", "Select * FROM
qryAuthsOwnersAddnlOwnersLienholders4OneCar")
One wonders why a redundant temporary querydef is created? And, does,
perhaps, qryAuthsOwnersAddnlOwnersLienholders4OneCar call a UDF that
creates new records?

What else?
Access/Jet is in error? Over 7 years posting and reading here I have
seen this, what, once for sure, perhaps another time or two, but these
latter have not been so serious. (The documentation is in error
frequently but that's another issue.)

Perhaps, there is something that you have not considered to be
important enough to tell us.

And perhaps you are in error when you report the varying results. From
far afield it is difficult to tell. When I come upon a situation like
this I say a few prayer-like words and rewrite the whole procedure from
scratch.

Jan 12 '06 #7

P: n/a
MLH
On 12 Jan 2006 13:39:40 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:
So what are we left with?

470 Set MyQdef = MyDB.CreateQueryDef("", "Select * FROM
qryAuthsOwnersAddnlOwnersLienholders4OneCar")
One wonders why a redundant temporary querydef is created? And, does,
perhaps, qryAuthsOwnersAddnlOwnersLienholders4OneCar call a UDF that
creates new records?

What else?
Access/Jet is in error? Over 7 years posting and reading here I have
seen this, what, once for sure, perhaps another time or two, but these
latter have not been so serious. (The documentation is in error
frequently but that's another issue.)

Perhaps, there is something that you have not considered to be
important enough to tell us.

And perhaps you are in error when you report the varying results. From
far afield it is difficult to tell. When I come upon a situation like
this I say a few prayer-like words and rewrite the whole procedure from
scratch.


I'll dig some more, to see what surfaces. Maybe I can strip out all
superflous material and post a bare bones illustration of the prob.
Hopefully, it'll resolve before then.
Jan 13 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.