Counting records that have salshes in a report 1

[mouseman]

I have an access report that has a field name A/E. I want to count the records for a specific A/E. For example, the records are in a table like this:

KF/CC
KF/MD

I would like to count only the KF, So it will show a count as 2

I used the following formula, but it did not seem to work.

=DCount("[A/E]","Broker Visits","[A/E]='KF*'&quot

Thanks for your help in advanced

 

[scriverb]

Change your DCount statement to the following(see Red):

=DCount("[A/E]","Broker Visits","[A/E] Like 'KF*'&quot

Does give you the correct results? You = 'KF*" was searching for the literal string KF* within [A/E]. Couldn't find any because you used the = rather than Like expression operator.

Bob Scriver

 

[DrSimon]

Not knowing the significance of the A/E field, my approach would be 'If I was going there I wouldn't start from here'.
Could you restructure the table and have an 'A' and 'E' field which are then concatenated on reports? That would be far more robust.

 

[mouseman]

Thanks Bob it works like a charm. Maybe you can help me with this other issue, i am having with the same report. I have another field name called Broker and it lists broker names. For Example,

Joe Doe/Tim Doe/Lucy Doe/
Mark Doe/
Steve Doe/Lisa Doe/

Is there a way I can do a count on these records so i can come up with 6.
I tried to use the following formula:

=DCount("[Broker]","Broker Visits","[Broker] Like'/*'&quot
When I use formula it come out with a count of 3 not 6.

Thanks again for your help

 

[scriverb]

This would require a seperate function or subroutine to count the slashes. Paste the following into a Module and then call it with: CountBroker() It will return a number which can be assigned to an object on the report. (i.e. text box property Control Source =CountBroker() )


Function CountBroker()
Dim db As Database
Dim rs As Recordset
Dim vNameStr As String
Dim vCount As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("table6", dbOpenDynaset)
rs.MoveFirst
vNameStr = Trim(rs("Broker&quot)
vCount = 0
Do
vNameStr = Trim(rs("Broker&quot)
Do
vCount = vCount + IIf(InStr(1, vNameStr, "/&quot > 0, 1, 0)
vNameStr = Mid$(vNameStr, InStr(1, vNameStr, "/&quot + 1)
If Len(vNameStr) = 0 Then
rs.MoveNext
End If
Loop Until Len(vNameStr) = 0
Loop Until rs.EOF
CountBroker = vCount
rs.Close
db.Close
End Function

Good luck

Bob Scriver

 

[mouseman]

Hi Bob, Thank you for your help, but I am having a little trouble with the module. I am not familar in working with modules. I want to say thank you for your patience.

The first thing I did is go to module and pasted the code that was listed above. Second, I went to the report and inserted a text box. Next, I added the function to the control source. The I went to view the report and it gave me an error, it said Compile Error: User-defined type not defined. Also I was not sure how to call the module like you said. You said call it this, with: CountBroker() Maybe thats the issue. Thanks again for your help Bob

Stephen

 

[scriverb]

The TextBox on your Report should have the following pasted into the Control Source property. The equal(=) sign is necessary.

=CountBroker()

you can also call a function in a query or in an experession in your VBA code.

example:

Dim vBrokerCount as Long
vBrokerCount = CountBroker()

Give the above a try and let me know.

Bob Scriver

 

[mouseman]

Bob,


In the text box I typed in =Count(), and I still got an error. The error says Compile Error: User-defined type not defined. The program brings up the module and it points to Dim db As Database. Your help will be greatly appreciated. Thanks

Steohen

 

[mouseman]

Bob,


In the text box I typed in =Count(), and I still got an error. The error says Compile Error: User-defined type not defined. The program brings up the module and it points to Dim db As Database. Your help will be greatly appreciated. Thanks

Stephen

 

[scriverb]

The function name is CountBroker so you have to put in the following:

=CountBroker()

NOT

=Count()

Bob scriver

 

[mouseman]

Hi Again, I tried your suggestion, and i am still getting an error. I am probably doing something wrong. The Database is a small file, is it ok if i send it to you? Thanks for help. The error that is coming up is Compile Error: User-defined type not defined. Thanks again

 

[scriverb]

Sure send it to me at scriverbl@ameritech.net

Bob Scriver

 

[mouseman]

Bob,

I sent you the Database, I have been having problems with my Email, for the past couple days. So let me know if you got it. Thanks again for your help.

Stephen

 

[scriverb]

I got the database but I only have ACCESS 97 available. It must be 2K or greater, correct?

Looking back on your error message the problem probably is that the ACCESS 2000 version doesn't recognize the DAO references in the function code. Change the Dim statements to the following and give it another try.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Let me know how that works. If it is still a problem I will try it on another computer that has 2000 and see if I can figure it out for you.

Bob Scriver

 

[mouseman]

Bob,

I made the changes you suggested and it is giving me the same error. So when you get chance, take a look at it for me. Thanks again, it is much appreciated.

Stephen

 

[mouseman]

Bob,

I found out I had to set a reference to DAO 3.6.
This is how I did it I open any module, then choose References from the Tools menu and select Microsoft DAO 3.6 from the list. This worked, but i having a problem with one more line of code. Set rs = db.OpenRecordset("table6", dbOpenDynaset, It does not recognize "table6", So I switched it with the true name Broker Visits, When i did that I got an mismatch error. So if you have any suggestions, please let me know. Thanks

Stephen

 

[scriverb]

Make sure that you surround your table name with double quotes:

Set rs = db.OpenRecordset("Broker Visits", dbOpenDynaset)


Also, just a word of caution. When naming tables, queries, forms, reports etc never name them with spaces embedded in the name. This just causes all sorts of problems in ACCESS. It requires you to be extra careful in referencing the object with quotes, sq. brackets, parenthesis, etc.

You should use the Underscore "_" to seperate names for ease of reading. "Broker_Visits" or "BrokerVisits". Both of the above work quite well.

Just a little help with naming conventions.

Please let me know if all works well with this last change.

Bob Scriver

 

[megmem]

Bob,

I made the necessary changes you specified and i am still getting an error. It is saying Runtime error 13, Type mismatch. If you have any other suggestions, let me know. Thanks for your help.

Stephen

 

[scriverb]

Copy and paste all of your existing code so I can take a look. There has to be a typo someplace as a Type Mismatch error doesn't make any sense here. It will be in the Declarations of the database, recordset or setting the values.

Just copy all of your code and paste it here and we will find it for you.

Bob Scriver

 

[mouseman]

Function CountBroker()
Dim db As Database
Dim rs As Recordset
Dim vNameStr As String
Dim vCount As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("Broker_Visits",dbOpenDynaset)
rs.MoveFirst
vNameStr = Trim(rs("Broker&quot)
vCount = 0
Do
vNameStr = Trim(rs("Broker&quot)
Do
vCount = vCount + IIf(InStr(1, vNameStr, "/&quot > 0, 1, 0)
vNameStr = Mid$(vNameStr, InStr(1, vNameStr, "/&quot + 1)
If Len(vNameStr) = 0 Then
rs.MoveNext
End If
Loop Until Len(vNameStr) = 0
Loop Until rs.EOF
CountBroker = vCount
rs.Close
db.Close
End Function

Thanks again Bob