Counting text in reports 2

[bazzanoid]

Hi all!

I'm trying to persuade a report to count the number of occurrences of particular words, e.g. 'Google' for a specified date range (as entered on form) and display the result on the report (as a number). I've tried every combination i can think of, but i can't get it to work! I've attempted to use =Count(IIf([Source]="google",0)) but all this does is count up the times it occurs in total, completely ignoring the given date range, yet every other field on this report is behaving properly within the date range....

any ideas?

Thanks!

Barry

 

[PaulBricker]

Try this

=Sum(IIf([Source] = "google", 1,0))


That should do it assuming [Source] only has one text value in the record. Otherwise, you would need to use 'Like'

=Sum(IIf([Source] Like "* google *",1,0))


Paul

 

[bazzanoid]

Thanks Paul - I've tried this, but it's not counting as per the date period set.

For example, it returns a result of 58 regardless whether the date is 2/1/05 to 2/28/05 or 3/1/05 to 3/14/05. Any ideas?

I've basically adapted an existing report, which takes the order date and identifies the suppliers etc based on the dates you give. There is a section "Order Date" which is hidden, and is dependant on the date range form as below:

>=[forms]![Report Date Range]![Beginning Order Date] And <=[forms]![Report Date Range]![Ending Order Date]

Any ideas?

 

[PaulBricker]

I assume you doing the counting using a Textbox on your report in either a group footer or the Report Footer? Can you tell us a little more about the data you are counting and where you are trying to do it.


Paul

 

[bazzanoid]

Hi Paul,

Thanks for the continued help - the easiest way to explain is by example...

I've uploaded a stripped-down version of our invoicing database to

http://www.rtd.uk.com/tektips/database.mdb

(16Mb) for you to take a look at if you don't mind - the report in question is Source Breakdown. I've left all the data in the system other than the personal details of our customers, naturally!

All i want is a report that will count the occurences of each source type as shown in the Source table, and display a summary, as the Sales Breakdown report does now. The only reason i'm having grief is because i'm a number man - the other reports work with item quantity numbers rather than text fields.

Thanks!

Barry

 

[PaulBricker]

I used expressions like this in the Report Footer with date range of 3/1/05 thru 3/15/05.

=Sum(IIf([Source2]="Ask Jeeves",1,0))
=Sum(IIf([Source2]="google",1,0))
=Sum(IIf([Source2]="Dealtime",1,0))
=Sum(IIf([Source2]="Web",1,0))
=Sum(IIf([Source2]="Yahoo",1,0))

Ask Jeeves returned 3, google returned 86, Dealtime returned 12, Web returned 48 and Yahoo returned 3 which were correct for what was showing on the report for those dates. I assume the RecordSource for the Report is working properly. I put it in a query and it seemed to run fine. As far as I can see, these expressions should work.

Paul

 

[bazzanoid]

Hi Paul,

I managed to get this bit down without a problem, but when i enter a different date range, i get the same results!

Any clues?

Thanks,

Barry

 

[PaulBricker]

I appears the problem is in your RecordSource. Try this in your report.

SELECT DISTINCTROW Customers.Source AS Source2, Customers.CustomerID FROM Source INNER JOIN (Customers INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID) ON Products.ProductID=[Order Details].ProductID) ON Customers.CustomerID=Orders.CustomerID) ON Source.Source=Customers.Source WHERE (((Orders.OrderDate) Between forms![Report Date Range]![Beginning Order Date] And forms![Report Date Range]![Ending Order Date])) GROUP BY Customers.Source, Customers.CustomerID ORDER BY Customers.Source;

In your RecordSource, you had
Select Distinctrow Customers.Source, Source.Source As Source2,.... etc.

and grouping by both values but Customers.Source has a many to one relationship with Source.Source so that was messing it up.

Try it out and let me know.

Paul

 

[bazzanoid]

Thanks Paul - I've modified the RecordSource and put in place all the coding for the different sources, I should have checked that using common sense really!

It's still not returning the figure based on date - again i tried to run it twice with the month of february and the month of march so far, but each time i get the same results of

Ask jeeves 1
Dealtime 7
Google 58
Kelkoo 16
Other 10
Previous 13
Recommended 0
Shop 10
Web 97
Yahoo 5

I'm getting really baffled by this now! Everything looks right! I've re-uploaded it to my web space again so you can see what has been done so far... this really is starting to drive me to the point of distraction now... It Should Work!

Thanks for your continuing assistance!

Barry

 

[bazzanoid]

Paul,

Before you delve too far - it appears to be working now - i had a bracket in the wrong place that i've corrected as it was uploaded and it works perfectly!

Thanks for all your help!

Barry

 

[PaulBricker]

Glad you've got it working. I was running out of ideas.


Paul