VBA IIF statement in Access

[jmoney30]

Hi,

I'm trying to do a multi condtion IIF statment and I keep getting error 3075 (missing operator). Please see my syntax
IIf(Count(Factor1.Factor) = 1, Temp.Value*Sum(Factor1.Factor), IIf(Count(Factor1.Factor) = 2 And Temp.Vendor =YYYY , Temp.Value*Max(Factor1.Factor),Null) As [Value]. I'm nut sure if it is missing parenthesis or qoutes around text field. I tried adding both but it still errors. If there's a better way using switch or fixing this would be a great help.

Thanks

 

[James006]

Hi,
Have you tested

IIf(Count(Factor1.Factor) = 1, Temp.Value * Sum(Factor1.Factor), _
IIf(Count(Factor1.Factor) = 2 And Temp.Vendor = YYYY, Temp.Value * Max(Factor1.Factor), Null))

 

[jmoney30]

I'll try and see if it works. The Temp.Vendor = YYYY is actually a string field for a name and not a date field. Should I put for example Temp.Vendor = "JOHN"? When I do this in VBA I get the end of statment error. I added more qoutes to escape it but it didn't work.

Thanks

 

[jmoney30]

Hi,
Have you tested

IIf(Count(Factor1.Factor) = 1, Temp.Value * Sum(Factor1.Factor), _
IIf(Count(Factor1.Factor) = 2 And Temp.Vendor = YYYY, Temp.Value * Max(Factor1.Factor), Null))

Hi I tried that and I get the same error

 

[Joe4]

Are you trying to do this in a calculated field in an Access Query/Table?

If so, note that those functions are only applied to the single individual record, and not all records in the entire query/table.
MAX, COUNT, and SUM are aggregate functions that are typically used in aggregate queries (or SQL code), and not used much in calculated fields (because they usually don't yield the desired result).

Also, what exactly is YYYY here?

Temp.Vendor = YYYY

If you are checking for a hard-coded value of "YYYY", you need to enclose it in double-quotes.
Otherwise it is looking for a field named YYYY.

You might be better off removing this calculated field for now, posting the SQL code of your query here (switch to SQL View), showing us a small sample of data being returned by the query, and telling us exactly what you are hoping to return.

 

[jmoney30]

Its VBA code using SQL to do the calculated value base on which condition is meet. The code works if it is based on only one condition but when I try the multi condition based on how I normally write it it says it's missing a parameter. I think it is something with commas or parentheses. I shouldn't wrote YYYY because it is confusing it is a string field but when I add the double quotes the VBA code reads it as an end of statment and throws an error.

 

[Joe4]

Its VBA code using SQL to do the calculated value base on which condition is meet. The code works if it is based on only one condition but when I try the multi condition based on how I normally write it it says it's missing a parameter. I think it is something with commas or parentheses. I shouldn't wrote YYYY because it is confusing it is a string field but when I add the double quotes the VBA code reads it as an end of statment and throws an error.

Can you post that full section of VBA code where you are applying it to SQL, posting the single condition version which is currently working for you?

 

[jmoney30]

Hi,
I got it to work. I had an extra parenthesis in the code. I had
IIf(Count(Factor1.Factor) = 1, Temp.Value * Sum(Factor1.Factor), _
IIf(Count((Factor1.Factor) = 2 And Temp.Vendor = SSSS, Temp.Value * Max(Factor1.Factor), Null))

and it should've been

IIf(Count(Factor1.Factor) = 1, Temp.Value * Sum(Factor1.Factor), _
IIf(Count(Factor1.Factor) = 2 And Temp.Vendor = SSS, Temp.Value * Max(Factor1.Factor), Null))

I inadvertently added the extra parenthesis.

Thanks,

 

[Joe4]

Excellent!
I am glad you were able to figure it out.