Problems using iif

[scottm]

Hello,

I am creating a report that will group together certain values to return a total value

The issue is, when I add more items to this list, it will return #error

the code in question is:

=Sum(IIf([disb] In ("A4 B&W","1","A4 B&W Duplex","Letter B&W Duplex","Letter B&W","Duplex A4 B&W","A5 B&W","A5 B&W Duplex","Duplex A5 B&W","Legal B&W","Other B&W Duplex","Legal B&W Duplex","Duplex Legal B&W","") And [DName] Like 'COPY*ROOM*',[CountItems],Null))

If I add more Paper Types (e.g. "Executive B&W" it starts to fail
The report contains multiple IIf statements similar to this (for other paper types etc)

The Values are actually retrieved via a vba script that looks up a table column and puts them into the iif statement in a string

Is there a better way to do this?

Thanks

 

[G81]

Not sure why it's erroring, but I would look towards the table structure to fix the issue and make it easier in the long run.

I would maybe look at having a table for your parts in your [disb] field. Then you can join to this in your query and get rid of the "IN" clause. You can then add to this table.

or if there is a rule to which [disp] parts you use in your query, you could use this in a select statement to input the correct parts in another query.

 

[DCrake]

=Sum(IIf([disb] In ("A4 B&W","1","A4 B&W Duplex","Letter B&W Duplex","Letter B&W","Duplex A4 B&W","A5 B&W","A5 B&W Duplex","Duplex A5 B&W","Legal B&W","Other B&W Duplex","Legal B&W Duplex","Duplex Legal B&W","") And [DName] Like 'COPY*ROOM*',[CountItems],Null))

you cannot sum nulls. change to 0

 

[scottm]

Regarding the Null, that part seems to work perfectly as it want it to be a blank field if no data exists, Is there a better way I should be doing this?

Sorry, I am a bit of a Beginner at access,
How would I do the join or select in the report for each value type?

The Paper Values ("A4 B&W","1","A4 B&W Duplex","Letter B&W Duplex", etc) are stored in [Disb].A4BW while the Data is pulled from [Data].disb
I cant adjust the imported data too much as its pulling from a live system which cant be adjusted but I can change the Disb Table to suit anything


Thanks,
Scott

 

[G81]

It may not be the best way, but it's how I normally do it.

If you manually add the paper types to the "IN" statement anyway, just add them all to a new table then create a new query. Then add both tables to the query and join the common fields ([Disb].A4BW with the new field in the new table) by dragging your mouse from one field to another. This is the same as doing a sql inner join.

then in the 1st column of the query have sometyhing like:

Sum([fieldwithcounts])

replace fieldwithcounts with whatever your field is called with the total for each paper type.

in the next column you can specify the "COPY ROOM" criteria in your dname field (then unshow it if not required to be seen).

Once your query gives the results that you want you can show it in the report by doing =[QueryName]![ColumnName]

----

Edit: Looking back at you original question though, this may not be a good solution for you. Especially if you have many different iif statements. Do you have an example of your iif statements? One which works and one which errors?