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

Count query

P: n/a
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!!!

Jan 12 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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" <lu*****@hotmail.com> schreef in bericht news:11*********************@g14g2000cwa.googlegro ups.com...
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!!!

Jan 12 '06 #2

P: n/a
Red
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.

Jan 12 '06 #3

P: n/a
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
Jan 12 '06 #4

P: n/a
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.

Jan 12 '06 #5

P: n/a
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.

Jan 12 '06 #6

P: n/a
I want to say "Thanks!" to everyone who replied.

Jan 12 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.