[Help] Wrong number of arguments?

[lovelykid23]

Hi guys,
I'm using some functions to build a report for my database. However, I got stuck at this one function.
So I created an unbound text box and add this function.

Technically, there are 6 groups of data: Home, Business, Daycare, School, Health Department and Other. These are divided further into groups with data >8, <8 and <=4, <4 and >0, and else.
I need to find percentage. So I created a function for the Other section that supposedly says if the total number of records that are not Home, Business, Daycare, School and Health Department is bigger than 0, then sum of the number of records of [Other>8] + [Other >=8 and <4] divide for the sum of number of records that are Other (which means they are not Home, Business, Daycare, School and Health Department) and they also need to be >8 or <=8 and >0.

I wrote this function

=IIf(Sum(IIf([Ordering kit for?]<>"Home" And [Ordering kit for?]<>"Business" And [Ordering kit for?]<>"Daycare" And [Ordering kit for?]<>"School" And [Ordering kit for?]<>"Health Department",IIf(Val([Test Kit Result])>8 Or Val([Test Kit Result])<=8 And Val([Test Kit Result])>0,1,0),0))>0,( Sum(IIf(Val([Test Kit Result])>8,1, IIf(Val([Test Kit Result])<=8 and Val([Test Kit Result])>4, 1, IIf(Val([Test Kit Result])<=4 and Val([Test Kit Result])>0,1,0),0),0)) / Sum(IIf([Ordering kit for?]<>"Home" And [Ordering kit for?]<>"Business" And [Ordering kit for?]<>"Daycare" And [Ordering kit for?]<>"School" And [Ordering kit for?]<>"Health Department",IIf(Val([Test Kit Result])>8 Or Val([Test Kit Result])<=8 And Val([Test Kit Result])>0,1,0),0))),0)

But Access keeps saying that the expression I entered has a function that containing wrong number of argument. What did I do wrong? How can I fix it?
Thank you very much guys

 

[Minty]

That's not a function - that is a very complicated and impossible to read nested IIf
I would write a function though - it will be much easier to read and debug.

 

[plog]

100% agree with Minty, but want to pile on more:

1. Not a function, that's one line of code. It should be a function. Open a module, write a function there, use as many lines as you need and then call that function instead of your monstrosity.

2. Horrible naming. You should only use alphanumeric characters and underscores in table/field names. That means no spaces or question marks in field names.

3. You need a new table. If the values "Home", "Business", "Daycare", etc. share a property, you need to create a table with a field that assigns them that property. I keep seeing you test for those values, instead you bring that new table into your query, link [Ordering kit for?] field to that new table and then just use that value.

4. Use parenthesis:

...Val([Test Kit Result])>8 Or Val([Test Kit Result])<=8 And Val([Test Kit Result])>0...

When you mix ANDs and ORs you need to seperate the pieces by using parenthesis. Without testing I honestly don't know if that above line will be evaluated like this:

(A OR B) AND C

Or like this:

A Or (B AND C)

There's a difference.


5. Redundant code. Using the code sample in #4, even with it being ambigous, one of those criterion is unnecessary. If its greater than 8 you don't need to test to see if its greater than 0.

Again though, #1, make this an actual function.

 

[plog]

#6. Why are you using the Val function? Please tell me its not because [Test Kit Result] is stored as text but you need to treat it like a number..

 

[lovelykid23]

Yup, I know that line is ridiculous I'm new to Access and slowly learning along the way. I never use Module before, but I'll look it up now to see what I can do with it.

And yes, I use Val() because the [Test Kit Result] is stored as text, not number. Besides all the legit numbers, the rest are things like "N/A", "none", "insignificant." My office did not keep the best records.:banghead:

I created a report based on a query that let me limit the data according to date and location. If I created an extra table now, how to make that table automatically limited by the same criteria as my query?

 

[plog]

If I created an extra table now,

Nope. You wouldn't create an extra table, you would create the proper table structure for your data and then import your existing data into it.

automatically

Nothing in existence is 'automatic'. Everything is triggered by a preceding event. Computers can do a lot of work for us, but something has to trigger their action.

make that table automatically limited by the same criteria as my query

Tables don't get limited. Think of tables as just well organized pile of memory on a computer. If you want to apply criteria you do it in a query.

With all that said, you should probably read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) that's the process of setting up database tables and the first step in working with any database. From there I'd work through some SELECT query tutorials (http://www.w3schools.com/sql/).