Count query

[dBNovice]

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!!!

[Arno R]

Re: Count query

This will work when your original PaperID is always 5 characters like in 004.1
Put this in the criteria-line of the field PaperID in the query grid:
Len([PaperID]=5

Or maybe something like Len([PaperID] <7 when you have 4, 5 or 6 characters originally...

Arno R

"dBNovice" <luv4nrt@hotmai l.com> schreef in bericht news:1137086716 .453103.61780@g 14g2000cwa.goog legroups.com...[color=blue]
> 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!!!
>[/color]

[Red]

Re: Count query

Wild problem..

let me get this straight.. with some examples...

PaperD
004.1
1.004.1
2.004.1
005.1
1.005.1

The count of the above would only be 2, right?

If so, this is how you could do it...I think...

Send your papeerid to a function to extrapolate only the correct info
(everything to the right of the second period), and have your query
count the unique results of that.

[guido via AccessMonster.com]

Re: Count query

You need to add your field "PaperID" to the query twice. The first will have
"Count" in the total row, the second will have "Where" in the total row and
Like("###.#") in the criteria row. Your query would look for the result of
the count (an integer) to be like ###.#

--
Message posted via http://www.accessmonster.com

[salad]

Re: Count query

dBNovice wrote:[color=blue]
> 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!!!
>[/color]

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(Paper ID)
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(strFie ld As String, strSearchFor As String) as Integer
Dim intPos AS Integer
Dim strHold As String
strHold = strField
Do while True
intPos = Instr(strHold,s trSearchFor)
If intPos > 0 then
strField = Mid(strHold,int Pos + 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.

[dBNovice]

Re: Count query

Thank you, I used the first option and it worked. I will try to use
the second option; I'm new to using VB in Access; so I can use the
practice.

[dBNovice]

Re: Count query

I want to say "Thanks!" to everyone who replied.