My code is not working HELP

[Ozzie1121]

I am trying to count the number of records for each agent in my table per date range. Example:


5/1/04 - 5/8/04
Agent Records
John 2
Mary 21
Charlie 10


My Code looks like this:

SELECT Leads.Agent, Count(Leads.Agent) AS CountOfAgent
FROM Leads
GROUP BY Leads.Agent
HAVING (((Count(Leads.Agent)) Between [Start Date] And [End Date]));


It is giving me an error that states:

This expression is typed incorrectly, or too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to a variables.


Please Help

 

[cmmrfrds]

Put the criteria in the where clause, having is evaluated after the summation and you are not aggregating the dates.

SELECT Leads.Agent, Count(Leads.Agent) AS CountOfAgent
FROM Leads
where(Leads.Agent Between [Start Date] And [End Date])
GROUP BY Leads.Agent

 

[Ozzie1121]

This code is not pulling anything. All I get are the column names but no agent names nor any numbers. Please help.

 

[nabiS2K]

I think the WHERE Clause is on the wrong field. I am going to assume that Leads.Agent is the name of the agent; however, you are using it with criteria as if it were a date like this:

WHERE (Leads.Agent Between [Start Date] And [End Date])

I assume you meant to use another field instead of Leads.Agent here.

 

[Ozzie1121]

Will I want to count how many times each agent's name has appeared. See I give sales leads to my agents that sell homes. I want to be able to pull up a specific date range to see how many leads have been given to each.

Another thing I am working on is that I have a query that pulls each lead for a specific date range for a specfic agent, on the footer of the last page of the report I would like a textbox to show the total number of leads that the report printed. I am using the "=SUM([Leads.Agent])" but it gives the "#error" message, I also tried it with "=SUM([Leads.Name])"; which is the names of the lead that was given to the agent.

 

[lespaul]

How is access going to sum a name? Don't you mean you want to count the number of occurrences?

Count(Leads.Name)

And as nabi states, there is no way the Leads.Agent will be between any dates. You need to change the criteria in the WHERE clause to:

SELECT Leads.Agent, Count(Leads.Agent) AS CountOfAgent
FROM Leads
where ([COLOR=blue]Leads.AssignDate[/color] Between [Start Date] And [End Date])
GROUP BY Leads.Agent

(change it to whatever the field name in LEADS that contains the date you are looking for)


Leslie

 

[Ozzie1121]

Thank You so much it worked. Can I bother anyone else for the second part about the report.

 

[lespaul]

What is the problem with the report?

leslie

 

[EBGreen]

Try
=COUNT([Leads.Agent]

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[lespaul]

I suggested that earlier Tom and assumed it didn't work since he's still asking about it?

Leslie


Leslie

 

[EBGreen]

Aah, didn't see that part of your post Leslie.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Ozzie1121]

Well it is giving me the #Error caption in the textbox. Well the report pulls each lead for a specific date range for a specfic agent, on the footer of the last page I put a textbox and labeled it txtNum I want it to show the total number of leads that the report will print. I am using the "=SUM([Leads.Agent])" but it gives the "#error" message, I also tried it with "=COUNT(Leads.Agent)" but it is still giving me the #Error.

 

[EBGreen]

Try counting using the name of some field that you display for each lead.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[Ozzie1121]

It is still giving me the #Error.

 

[hymn]

if you are counting a text field try
ABS(sum(Leads.Agent])

Hope this helps
Hymn

 

[Ozzie1121]

The ABS(SUM[Leads.Agent]) is still giving me the error.

 

[nabiS2K]

Try this in the box:
SUM(CountOfAgent) since CountOfAgent is name of the Count field. ;-)

Sorry I didn't reply sooner... I've been in lala-land... it's beautiful this time of year.

 

[Ozzie1121]

Well the CountOfAgent is on another report not this one. On this report I only show the name of the lead and all the information that goes with the lead like, the date the lead called, lead's phone number, etc... I want the total number of leads to show up on the the textbox I created, txtCount. Also this report is just for one of the agents. I have tried SUM(Leads.Agent), SUM(Leads.Name) which is the column for the leads name but neither gives me the result I want. Hope this helps.

Next time you go to LaLa-Land tell my co-workers to come to work, seems they are there alot.

 

[nabiS2K]

Did you try Count(Leads.Agent) as Leslie suggested?

This should work...

 

[PHV]

Have you tried Count([NameOfLeadsAgentControl])
ie the name of the control, not the name of the field.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244