Need to cound distinct fields in a report 3

Dumboy · Mar 9, 2000

I'm back... this is a easy one for you guys.. 
I need to count total PO Numbers in a report as a summary. some of the PO Numbers are duplicated and I need a count of the unique PO Numbers. I have tried the distinctcount thing at the field level but it keeps counting all of them. 
 
is it possible to do something like 
=Count([PONumber]) 
in a unbound control box that will ignore duplicates...? 
 
Thanks..... Dumboy..! Ouch..! ..my brain h <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href=

http://www.planetwebit.com>Planetwebit.com</a> Hire

me... I need the money....! Pleaaaaaaseeeeeee....... !

DougP · Mar 9, 2000

OK this my seem goofy but it works 
Paste this function in a module 
----------------------- 
Public Function CountDistinct(Field) 
Dim db As Database, rst As Recordset, Temp As Single 
Set db = CurrentDb 
SQL = "SELECT [PONumber] FROM YourTable GROUP BY [PONumber];" 
Set rst = db.OpenRecordset(SQL) 
rst.MoveLast 
Temp = rst.RecordCount 
rst.Close 
db.Close 
CountDistinct = Temp 
End Function 
--------------- 
 
In the "SQL" line change "YourTable" to your table 
and change the PONumber to what yours is if it's not POnumber. 
 
put this in your Textbox on your report to get the count 
 
=CountDistinct([PONumber]) 
 
Thats it. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Dumboy · Mar 10, 2000

Thanks DougP 
 
But I think I broke your code.... when I ran it... there is one of those horrible Windows messages with like a letter i but upside down (isn't that horrible...?) anyway it tells me 
i (upside down inside a little yellow triangle) Compile error 
Variable not defined 
 
Then I stared at the screen for half and hour and nothing else happend so I clicked the OK button and things got worse. It took me to another screen where amazingly the code you gave me was there....! 
 
There was also a yellow arrow -> on the Public Function line (I did not put it there...) 
and the word SQL was highlited in blue. 
Public Function CountDistinct(Field) 
Dim db As Database, rst As Recordset, Temp As Single 
Set db = CurrentDb 
SQL = "SELECT [PONumber] FROM YourTable GROUP BY [PONumber];" 
Set rst = db.OpenRecordset(SQL) 
rst.MoveLast 
Temp = rst.RecordCount 
rst.Close 
db.Close 
CountDistinct = Temp 
End Function 
 
Since I have no clue as far as VB (which I think means VeryBright) I don't know how to define the variable. 
 
Man... can you fixed the code.... the other day I also broke the Internet.... that's another story... 
 
Thanks in advance for telling me how to fix my mess..... 
 
Regards, 
 
Dumboy..! Ouch..! ..my brain h <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href= > </a>

Dumboy · Mar 10, 2000

DougP.... I did change YOURTABLE with the name of my Table (actually a Query named GeneralReportQuery and I tried with and without the squeare brackets 
 
Dumboy..! Ouch..! ..my brain h <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href= > </a>

elizabeth · Mar 10, 2000

Dumboy, Love reading your posts!

"SQL" is a reserved word. Access won't let you use it as a variable. Change "SQL" to "strSQL" and add the line below to the line immediately after the other one that starts with "Dim": 
 
Dim strSQL as string 
 
That will define your variable for you. Good luck

elizabeth · Mar 10, 2000

Sorry, typo: each Dim statement needs to be written on it's OWN line.

Dumboy · Mar 10, 2000

Hey.....! that worked.... Thanks Elizabeth..... Thanks DougP......! 
 
I got to start learning that VB thing......! please recomend any books or places to start from Zero...... also if you know about the reproductive habits of Neurons let me know... I need to populate my brain......! so many good neurons lost during my life...! and they don't grow on trees..... 
 
Regards, Dumboy.! Ouch.! .my brain..... <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href= > </a> It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

elizabeth · Mar 10, 2000

what version of access are you using?

DougP · Mar 10, 2000

I bought this book and that book and got something out of one and something differnt out of another. 
Go to <A HREF="

http://www.Amazon.com"

TARGET="_new">

http://www.Amazon.com</A>

and type in "Visual Basic" there should be a ton of them. 
The best way to learn this stufff is using it. 
I have been a consultant, a Programmer, System Administrator 
Of course Elizabeth will attest to this we both have spent way too much time on our butts in front of a PC. I'm talking years DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Dumboy · Mar 10, 2000

Elizabeth ... Moi cheri...I'm using Access 97 sr2 
 
DougP... so, you want me to go to Amazon.com and buy all the books on Access...? 
do you think I'm made of dough....? (mmmhhh...! makes me think...you are probably associated with Amazon or at least stock holder, aren't you?) 
 
Isn't there a book (you can recommend) that has exercises like step by step little programming so I can get familiar with the VB thing.... once introduced to the concept I can branch out and let my neuron fly and express creativity by writing millions of code and VB programs...! 
 
I don't really want to.... but I'm sure there is a VB for Dummies book outhere... of course I will have to first read the VB for Idiots before I move up to the Dummies book...! 
 
Please let me know of any beginers resources... and again thanks for the help the code worked great. 
 
Regards 
 
Dumboy.! Ouch.! .my brain..... <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href= > </a> It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

DougP · Mar 10, 2000

You're right there is a book out there. 
NO I don't have any to recommend. I don't have any at all. 
I am looking at my book shelf right now. 
My recommendation was to go to Amazon. 
I have looked there and they do have a lot to choose from 
Pick one or two 
Or go to the book store and thumb through them which is better cause you can look at the way each Publisher presents the data. 
Everybody is different, some people like one book over another. 
The fastest way to get an answer Cheaply is to come here to Tek-Tips. 
The way I write a program so quickly is cause I have a folder on My server called "DougCode". I can get to it from anywhere in the building. 
I put a shorcut to it in my "Start" button so I can open that folder in one second. 
I has code snippets in ".txt" files for things I've done over the years. There are 184 files in there right now. 
I don't have to remeber exact syntax. 
I just look in the folder and find one that might do what I need and copy and paste the code from it into my Access VBA code window. 
The text file also has instructions of particulars involved. 
 
So grab the code above here that works and create your own "Dumboy-Code" folder and be on your way. 
OK DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Dumboy · Mar 13, 2000

DougP... can I call you daddy.....? 
 
Thanks I have started my very own Dumboy code and I saved in a file called Dumboy code which I carry with me on a little floppy so I can access it anywhere in the building... sorry I don't have snippets... (I'm partial to dogs myself...) but once I learn more about VB I will get me a couple of those... (I imagine they get along with dogs just fine... right...?) 
 
Well thanks again.... and be sure.... I'll be back.... I wish somebody came up with a really simple, stupid question so I can help somebody for a change..... 
 
Later..... Dumboy.! Ouch.! .my brain..... <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href= > </a> It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

elizabeth · Mar 13, 2000

I'm just finishing up with a Microsoft Press CD called "Mastering MS Access 2000 Programming". It is very basic, disappointingly so in my case as I thought I would be getting something more advanced. But it gets the main points across which can be fleshed out by other reference material. If there is a 97 version I would recommend that based on this. Do you have the MS reference documentation for Access 97? It is a pretty complete intro. But while you're at it why not try to talk your employer into buying you Access 2000 Developer edition with all of the documentation.

Dumboy · Mar 13, 2000

Thanks Elizabeth..... I'm already browsing books at Borders books.... dammed... bastards... only two days and I'm already addicted to their coffee..... I found a couple good books one from Waite... on VB... (I learned it stands for Visual Basic...!) 
 
Hey... if that Microsoft Press CD you have is bothering you... you can always send it to me...... :-] 
 
Thanks again... Later....... aaargggghhhh....oUcH...!....! Dumboy.! Ouch.! .my brain..... <a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a> <a href= > </a> It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need to cound distinct fields in a report 3

Dumboy

Technical User

DougP

MIS

Dumboy

Technical User

Dumboy

Technical User

elizabeth

IS-IT--Management

elizabeth

IS-IT--Management

Dumboy

Technical User

elizabeth

IS-IT--Management

DougP

MIS

Dumboy

Technical User

DougP

MIS

Dumboy

Technical User

elizabeth

IS-IT--Management

Dumboy

Technical User

Similar threads

Part and Inventory Search

Sponsor