Error 3122

[jollofrice]

Every time I run the following query:

SELECT SchedRepQuery_1.LicensePlate, SchedRepQuery_1.VehicleName, IIf((Count([SchedRepQuery_1.Event1]<>"-")=1),[SchedRepQuery_1.Event1],(IIf(Count([SchedRepQuery_1.Event1]<>"-")>1,"CONFLICT","IDLE"))) AS Expr1
FROM SchedRepQuery AS SchedRepQuery_1;

I get runtime error 3122: "You tried to execute a query that does not include the specified expression "*" as part of the aggregate function.

The * equals LicensePlate as the query is written above, but if LicensePlate is removed it will show VehicleName, which if removed, will show the Expr1 equation.

Can anyone tell me why this error is occuring or how to fix it?

 

[stopher]

What are you trying to achieve with the IIF expression?

Stopher

 

[jollofrice]

I have a list of events for a number of vehicles. I want to display a week's schedule with one row for every vehicle, with the days serving as column headers. Currently, the report created a row for every event recorded for a vehicle. The IIF expression was supposed to create one row that would tell me if the vehicle had one assignment, no assignment, or conflicting assignments.

 

[stopher]

I have a list of events for a number of vehicles. I want to display a week's schedule with one row for every vehicle, with the days serving as column headers. Currently, the report created a row for every event recorded for a vehicle. The IIF expression was supposed to create one row that would tell me if the vehicle had one assignment, no assignment, or conflicting assignments.

Do this in stages

Create an aggregate query (using the sigma sum sign) and COUNT the number records for a vehicle on a given date i.e. it should have GROUP BY in the SQL

Write a second query based on the first to decode the count field into a text value using an IIF statement

Then you can create a crosstab query based on the second to put your days as columns.

I don't have time to do an example just now but if you're stuck, post you d/b.

Stopher

 

[jollofrice]

Okay, thanks. How do I get it to count events by date instead of total records?

 

[jollofrice]

Nevermind. I think I got it. Thanks a million!

 

[jollofrice]

Actually, I do have another question. How do I get it to count to zero? Currently my query only shows records with matching criteria that count one or higher, but I need it to show records with zero matching criteria as well.