Hi Rich:
Glad it helped you out. You know you might want to change the function to return an integer with the actual ordinal position (1....n) - instead of a string with 'Yes' or 'No', and then change your final query's criteria for that field to 'between 1 and 5'
I had it that way at first, but couldn't apply criteria against it the field (I had forgotten that you can't apply criteria against a function generated field). I changed the integer returned to a 'yes'/'no' just to experiment. Then I remembered that I had to do a make tble first. Its probably a better design to save the actual ordinal number – then you could just change report criteria if the need changed.
Here is function code:
Function AssignNumbers(deptcity As String) As Integer
Static strDeptCity As String
Static intNumber As Integer
If strDeptCity <> deptcity Then 'first time through or new deptcity
intNumber = 1
strDeptCity = deptcity
Else 'still on the same deptcity, increment counter
intNumber = intNumber + 1
End If
AssignNumbers = intNumber
End Function
RE: the problem with running the function in your database: When you finished copying the function over to your database, did you compile it? (Option under Debug menu). I think thats happened to me when I hadn't compliled in the past??
Normajean