different recordcounts on report than it's source query

[MontyBurns]

Hi,

I've got a report which is returning some duplicate rows (total of 29), even though it's underlying query is only returning 23, non-duplicated rows.

I have no idea why this is - I have no code worth talking about in the report. I have a custom ordering function called in the query, but can't see how this would affect things.

Please help!
Burns

 

[Guest_imported]

Hi,
Why don't you post your custom ordering function so that someone can take a look at it and see if it has any flaws?

 

[Guest_imported]

Hi,
Before you post your function tyr running the query with one of the built in ordering functions. If it works fine, then the problem probably lies with your function. Keep us posted.

 

[MontyBurns]

ok, here's my ordering function, after the query the report runs from:

SELECT tblTopicsInReview.TopicId, tblTopicsInReview.LegalTopicNo, tblTopicsInReview.LegalTopic, tblTopicsInReview.HPCompleted, tblTopicsInReview.InputCompleted

FROM tblTopicsInReview INNER JOIN (tblExistingFiles INNER JOIN tblTopicsToFiles ON tblExistingFiles.FileNo = tblTopicsToFiles.FileNo) ON tblTopicsInReview.TopicId = tblTopicsToFiles.TopicID

WHERE (((tblTopicsInReview.InputCompleted)=True))
ORDER BY MakeFileIdSort([tblTopicsInReview].[LegalTopicNo]);

______________________________________________

Function MakeFileIdSort(varSrc As Variant) As String


Dim szTemp As String
Dim szSrc As String
Dim szSub As String
Dim intSize As Integer
Dim intCount As Integer
Dim varDest As Variant
Dim intNumberFound As Integer


If Not IsNull(varSrc) Then
' Initialise variables
varDest = ""
szSub = ""
intNumberFound = False
intSize = Len(varSrc)

For intCount = 1 To intSize
' Get next character
szTemp = Mid$(varSrc, intCount, 1)

If IsNumeric(szTemp) And (szTemp <> &quot;.&quot Then
' build up numeric sub string
intNumberFound = True
szSub = szSub & szTemp
If (intCount = intSize) Then
' End of string append last numeric string
varDest = varDest & Format$(szSub, &quot;0000&quot
End If
Else
If (intNumberFound = True) Then
' Numeric string has been built. append it in new format
varDest = varDest & Format$(szSub, &quot;0000&quot & szTemp
intNumberFound = False
szSub = &quot;&quot;
Else
varDest = varDest & szTemp
End If
End If
Next intCount
End If
MakeFileIdSort = varDest


On Error GoTo MakeFileIdSort_Err

MakeFileIdSort_Exit:
Exit Function

MakeFileIdSort_Err:
MsgBox &quot;MakeFileIdSort - &quot; & Error$ & Err
Resume MakeFileIdSort_Exit

End Function


The function/query takes the LegalTopicNo field as an input and generates a new column derived from this column, which a std sort can be performed on. It's needed as LegalTopicNo is in the form of 20.9 and 20.13 - where 20.13 should appear after 20.9 (so it's thirteen, not point one three). Basically it splits each char out and makes it into a uniform length string, adds a dot and then appends the next char, giving you something like: 0020.0013

I just can't see how this would affect the report and not the query?

Still baffled,
Burns