Count specific data in a field

[Daina]

Greetings,

I'm having a mental block, I know this is easy but I just can not seem to work it out with the help function. I have a report with a field called "Complaint Method" which is connected to a combo box and has a few choices eg. Letter, Telephone,Fax, email. what I need to achive on my form is a count of separate entries on the form eg.
Total Telephone =
all of the methods I have tried so far have failed, I am able to count all the entries on the page but this is not what I need, can anyone make any suggestions pleaseeeeee.

Getting frustrated

Dee

 

[LittleSmudge]

My approach Dee would be to put some code in the Section's On_Format event that open's a recordset using the following SQL string


rst.Open "SELECT Count(PrimeKeyField) AS CountOfLetters " _
& "FROM tblTableName " _
& "WHERE ComplaintMethod = 'Letter'"

TotalLetter = rst!CountOfLetters
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfPhones " _
& "FROM tblTableName " _
& "WHERE ComplaintMethod = 'Telephone'"

TotalPhones = rst!CountOfPhones
rst.Close

etc .. ..

Where TotalLetters, TotalPhones etc are controls on the report where you want the values displayed.



'ope-that-'elps.

G LS

 

[Daina]

LittleSmudge,

Thank you for your reply by "the Sections On_Format event" do you mean the report, or the text box that I am adding to the bottom of the report in order to display the results of the different fields on the page. I have looked at the properties on both and can not find an "On_Format" in either the report or the text box. Or am I totally barking up the wrong tree?

confused!!

Dee

 

[LittleSmudge]

No, you're not barking ... , just a little off target.

In a report each section Formats separately.

A section is, for example Report Header, Page Header, Detail, Report Footer, etc .. ..

You need to put the code I mention in the On-Format event for the section where the control itself actually is.
( I wasn't sure which Section you're using so I made to comment generic. From your reply - it now seems you're using either the Page Footer or Report Footer )



'ope-that-'elps.

G LS

 

[Daina]

LittleSmudge

Thank you for your help I have hopefully adapted the code you offered correctly. Yes I am adding this to the page Footer of the report and I found the On-Format that you were referring to. I have also included what I have interpreted out of the code you suggested. Please note this database was not built by me. The table used is tblComplaints then a qryComplaintsDateRange which prompts for a start date and an end date and the report rptComplaintsDateRange is what I am working with. this is the code I have adapted to cover all fields. I hope I have adapted it correctly please note all options in the complaint method field are typed in capitals. I will await your reply before adding this code.'

rst.Open "SELECT Count(PrimeKeyField) AS CountOfTel " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'TEL'"

TotalPhones = rst!CountOfTel
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfFax " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'FAX'"

TotalPhones = rst!CountOfFax
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfEmail " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'EMAIL'"

TotalPhones = rst!CountOfEmail
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfPerson " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'PERSON'"

TotalPhones = rst!CountOfPerson
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfLetter " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'LETTER'"

TotalLetter = rst!CountOfLetter
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfCourier " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'COURIER'"

TotalPhones = rst!CountOfCourier
rst.Close

Again thank you for all your help

Dee

 

[Daina]

LittleSmudge,

I entered the code listed below in to the OnFormat of the page footer and it comes up with the following error:

Complie Error
Variable not defined

obviously I have gotten something wrong somewhere.

Can I not just add a text box to the page footer and enter an expression into the control source to display a total? the same as I have for a birthday field on another form e.g. =Format(Now()-[Date of Birth],"yy&quot. This to me seems a little simpler, but then again I am pretty simple person who tends to live by the KISS principle. ha ha

I am having difficulty understanding how I am going to have separate total boxes in the footer for each of the complaint methods from this SQL string in the page footer?


Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer)
rst.Open "SELECT Count(PrimeKeyField) AS CountOfTel " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'TEL'"

TotalPhones = rst!CountOfTel
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfFax " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'FAX'"

TotalPhones = rst!CountOfFax
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfEmail " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'EMAIL'"

TotalPhones = rst!CountOfEmail
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfPerson " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'PERSON'"

TotalPhones = rst!CountOfPerson
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfLetter " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'LETTER'"

TotalLetter = rst!CountOfLetter
rst.Close

rst.Open "SELECT Count(PrimeKeyField) AS CountOfCourier " _
& "FROM tblComplaintMethod " _
& "WHERE ComplaintMethod = 'COURIER'"

TotalPhones = rst!CountOfCourier
rst.Close


End Sub

Getting stressed

Dee

 

[LittleSmudge]

Dee,

I've been away for a week, getting rained on in Windermere, so appologies for the delay in the reply.

In the footer you need text box controls for the totals to go into.
The names of these text box controls should be
TotalPhones
TotalFax
TotalEmail
TotalPerson
TotalLetter
TotalCourier

And there is a series of repeated typos in my previous code

TotalPhones = rst!CountOfFax
rst.Close

should be

TotalFax = rst!CountOfFax
rst.Close

and

TotalPhones = rst!CountOfEmail
rst.Close

should be

TotalEmail = rst!CountOfEmail
rst.Close

etc .. ..


'ope-that-'elps.

G LS