Solved: user defined function

[tkaplan]

i am trying to create a user defined function to do the following

i have a table that has about 20 fields. field are id, a1, a2, a3, a4, a5, b1, b2, etc. through e4, e5.
i am writing a query that i would like to have the following:
id, a: if(a1="X",1,0)+if(a2="X",1,0)+if(a3="X",1,0)+if(a4="X",1,0)+if(a5="X",1,0) and repeat through e.

so that i will end up with
id, a, b, c, d, e
so that in fields a through e i will have a count of how many X's there are.

I want to write a function (or is there one already defined?) so that i can do:
id, a: conditioncount(a1,a2,a3,a4,a5), b:conditioncount(b1,b2,b3) (i should be able to include up to 5 fields - one is mandatory, everything else is optional.)

so i imagine it would be something like:

function conditioncount(byval fieldvalue as ....)'not sure what

dim numFields as long
dim i as integer
i=0
numfields='amount of fields being passed
'assign all fields to array?
for each field in array
i=i+iif(fieldvalue="X",1,0)
loop

'function should return the value of i
end function



how can i do this?

thank you,
tkaplan

[Tommy]

Hi tkaplan,

I rewrote the function posted for what you are requesting, but am unsure if this is what is required. Pretty sure someone will drop by with a better idea/way. :)


Function conditioncount(fieldvalue()) As Long
Dim i As Long
Dim j As Long
For j = 0 To UBound(fieldvalue)
i = i + IIf(fieldvalue(j) = "X", 1, 0)
Loop
conditioncount = i
End Function

[tkaplan]

i will try it and see if it works for me.
what does the UBound() function do?

[Tommy]

The Ubound function tells how many elements are in the array fieldvalue. There is a count function in Access but I don't know how to use it, that is to say I am not sure if the count will be for the whole column, if it is for a row, or if you can specify some criteria that it counts.

[tkaplan]

I tried doing that but it is not working for me.

I put in the query:
A: ConditionCount([TrendData]![A1],[TrendData]![A2])
and it told me that it's the wrong number of arguements.

I tried
A: ConditionCount([TrendData]![A1])
and it returns #error.

I am attaching the database (consists of 1 table and 1 query and 1 module)

[Tommy]

You Have choices!:)
If the data in the table is not so much to change, the fast approach would be to change the data to numbers as in 1 for "X" and 0 for "". If the data is too much to change, the database example I have attached will work but it is SLOW. I did get it to work both ways so.

Happy coding

[tkaplan]

i cant change to 1's and 0's.
your solution will work, however i really needed something that i can just say
conditioncount(var1,var2,var3...) and it will count them all for me no matter how many variables.

anyone know of a way to do this???

[Tommy]

I attached the database with this in it. The curent version will handle 13 arguments, more can be added if needed.

SQL:
SELECT TrendData.Date, TrendData.Center, TrendData.Area, TrendData.Region, ConditionCountd([TrendData]![A1],[TrendData]![A2],[TrendData]![A3],[TrendData]![A4],[TrendData]![A5]) AS Expr1, ConditionCountd([TrendData]![B1],[TrendData]![B2],[TrendData]![B3]) AS Expr2, ConditionCountd([TrendData]![C1],[TrendData]![C2],[TrendData]![C3],[TrendData]![C4],[TrendData]![C5]) AS Expr3, ConditionCountd([TrendData]![D1],[TrendData]![D2],[TrendData]![D3]) AS Expr4, ConditionCountd([TrendData]![E1],[TrendData]![E2],[TrendData]![E3],[TrendData]![E4],[TrendData]![E5],[TrendData]![E6],[TrendData]![E7],[TrendData]![E8]) AS Expr5, ConditionCountd([TrendData]![F1],[TrendData]![F2],[TrendData]![F3],[TrendData]![F4]) AS Expr6
FROM TrendData;


Revised ConditionCountd

Function ConditionCountd(Optional fldvA = "", Optional fldvB = "", _
Optional fldvC = "", Optional fldvD = "", Optional fldvE = "", _
Optional fldvF = "", Optional fldvG = "", Optional fldvH = "", _
Optional fldvI = "", Optional fldvJ = "", Optional fldvK = "", _
Optional fldvL = "", Optional fldvM = "") As Long
Dim i As Long
If fldvA = "X" Then i = i + 1
If fldvB = "X" Then i = i + 1
If fldvC = "X" Then i = i + 1
If fldvD = "X" Then i = i + 1
If fldvE = "X" Then i = i + 1
If fldvF = "X" Then i = i + 1
If fldvG = "X" Then i = i + 1
If fldvH = "X" Then i = i + 1
If fldvI = "X" Then i = i + 1
If fldvJ = "X" Then i = i + 1
If fldvK = "X" Then i = i + 1
If fldvL = "X" Then i = i + 1
If fldvM = "X" Then i = i + 1
ConditionCountd = i
End Function

[Tommy]

Hi tkaplan,

LOL I found a better way :)
The ParamArray means that the function ConditionCount will take any number of aurguments. Usage would be: ConditionCount([TrendData]![C1],[TrendData]![C2],[TrendData]![C3],[TrendData]![C4],[TrendData]![C5]) AS Expr3

In the attachment the query name is UseThisOne.:rofl:

Function ConditionCount(ParamArray fieldvalue()) As Long
Dim i As Long
Dim j As Long
For j = 0 To UBound(fieldvalue)
If fieldvalue(j) = "X" Then i = i + 1
Next
conditioncount = i
End Function
End Function

[tkaplan]

Perfect:)

Exactly what I needed. I knew there was a way to do it, I just didnt know the paramarray keyword.

Thank you so much for all your time.

Tkaplan