Iif statement in group footer

[Rik_StHelens]

Hi

I have an issue with group footers in a report (see: http://www.access-programmers.co.uk/forums/showthread.php?t=183047)

As my footer ignores all but the last record in a group, i am using an IIF statement to get the values i want to display in the group footer (the detail will only display on screen, and only the footer is printed to create a summary report)

The statement is as follows:


=iif(count(iif([Jan]=”Checked”, 1, 0)=1, “Checked”, “”))


A vehicle is only going to be checked up to once a month, so this should work, but i am getting an error message "the expression you entered has a function with a wrong number of arguments" and i thought i'd move my post now that i was no longer trying to work at query level. (please see my other post for an excel file explaining what i am trying to achieve visually)



Thanks for your time and help

 

[stopher]

I'm not exactly sure what you want but maybe this will give you some help:

TRANSFORM First(IIf([InspectionDate]>#6/6/2008#,"Checked","")) AS Checked
SELECT tblVehicle.Customer, tblVehicle.FleetName
FROM tblVehicle LEFT JOIN tblInspection ON tblVehicle.RegistrationNumber = tblInspection.Registration
GROUP BY tblVehicle.Customer, tblVehicle.FleetName
PIVOT Format([InspectionDate],"mmm") In ("","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

I've put a specific date in here while I was testing with your data but of course you can change back DATE().

Note that I've also included the empty string "" in the column headings as your select query includes records with no dates.

hth
Chris

 

[Rik_StHelens]

Thats perfect!!

Thanks very much. I've been plugging away at this one since last week. We used to copy and paste by hand, and it took hours.

Now i've just got to do the same thing with costs per vehicle.

Thanks a lot

 

[Rik_StHelens]

I have changed your code for another query pulling the costs by vehicle, and it works fine. However, how would i go about putting in a totals column at the end (to show cost of all work on a vehicle in last 12 months)

Sorry to be a pain, but i appreciate the help!

Here is the SQL:

TRANSFORM First(IIf([INVDATE]>Date()-365,[TNETT],"")) AS Checked
SELECT tblVehicle.Customer, tblVehicle.FleetName, tblVehicle.RegistrationNumber AS Reg
FROM TRNLIST RIGHT JOIN tblVehicle ON TRNLIST.REGNUM = tblVehicle.RegistrationNumber
WHERE (((tblVehicle.Customer) Like "*" & [Customer Name] & "*"))
GROUP BY tblVehicle.Customer, tblVehicle.FleetName, tblVehicle.RegistrationNumber
PIVOT Format([INVDATE],"mmm") In ("","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Rik_StHelens]

Daft question

i've sorted it.

thanks again!