Aggregate/Crosstab Date Query ???

[Maxwell Britman]

Hi,

I have a problem with a query which just isnt working.

I have a customers table with JoinDate and AgentID (FK from Agents
table). I would like a query to show total sales for each Agent today
or on a certain date.

The sql query I have is:

SELECT DISTINCTROW tblAgents.AgentName, Count(*) AS [Count Of
tblCustomers]
FROM tblAgents INNER JOIN tblCustomers ON tblAgents.AgentID =
tblCustomers.AgentID
WHERE (((tblCustomers.ConnectionDate)=Now()))
GROUP BY tblAgents.AgentName
ORDER BY tblAgents.AgentName;


A similar query without the WHERE condition works fine, but this does
not. Any clues as to what I'm doing wrong? or is this not possible?

I also tried using begin and end date variables via a dialog as
recommended on Allen Browne's website, but that didnt help. He
mentions that this wont work with aggregate or Transform queries. Is
this the case here with mine?

Any help would be appreciated.

Thanks in advance

Max

 

[Allen Browne]

Are you getting an error message? Or just no records?

Now() represents the date and time of the moment when the query runs. Unless
tblCustomers.ConnectionDate had been set up to have a match for the exact
moment you ran the query, it would not return any records.

If the ConnectionDate contains the date only (no time component), you could
try Date() in place of Now():
WHERE tblCustomers.ConnectionDate = Date()

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Maxwell Britman" <maxwellbritman.wellwhatcouldpossiblygohere.lineone.net@>
wrote in message
news:[email protected]...

 

[Maxwell Britman]

Allen,

I was not getting any records before. ConnectionDate is in Short Date
format and I did try Date function before but I forgot to add () after
it. Duh!

Thanks very much. It works perfectly now!


Max