dBNovice wrote:
Hey group,
I am trying to do a count of the number of papers in a table. The
table has a PaperID that differentiates each paper , e.g. 004.1. Some
papers are reused. The reused paper is given a new PaperID. The
PaperID includes 3 new numbers appended to the original PaperID, e.g.
664.004.1.
When I do a count, I do not want to count the reused paper. I set up a
count query and had the criteria { Not Like "***.***.*" }. I have also
tried the criteria { Not Like "###.###.#" } ,
{ Like "***.*" } and { Like "###.#" } but neither gives me the correct
count. Please help!!!
Perhaps you could check on the length of the field. Let's say you'd
never have a number greater than 004.9999 unless used. You could then
create a column
Expr1:Len(PaperID)
and in the criteria
< 8
You could also create a function and put it in a code module. This is
for A97. You may be able to use SPLIT() to determine the number of
array elements in higher versions. Ex:
Function OccurCnt(strField As String, strSearchFor As String) as Integer
Dim intPos AS Integer
Dim strHold As String
strHold = strField
Do while True
intPos = Instr(strHold,strSearchFor)
If intPos > 0 then
strField = Mid(strHold,intPos + 1)
OccurCnt = OccurCnt + 1
Else
Exit Do
Endif
Loop
End Function
Now in your query you can create a column like this
Expr1:OccurCnt([PaperID],".")
to search for all occurrences of a period and in the criteria enter
1
since those reused will have more than 1 period.
