Adding iif statements In calculated Field

[mrmarc]

Having some problems with a calculated field.

In my head this should work:

IIf([Grade] Like "*SS1*",[NumberOfCases]*28,0) Or IIf([Grade] Like "*SS2*",[NumberOfCases]*28,0) Or IIf([Grade] Like "*11P*",[NumberOfCases]*28,0) Or IIf([Grade] Like "*11S*",[NumberOfCases]*28,0)

I am getting results like "-1", but basically I need a formula that will multiply the "NumberOfCases" if it is a specific "Grade".

Thots?

 

[jarjarbingie]

I suggest to put the formula in the data source as a helper column instead, then use that helper column in the pivot table.

Having some problems with a calculated field.

In my head this should work:

IIf([Grade] Like "*SS1*",[NumberOfCases]*28,0) Or IIf([Grade] Like "*SS2*",[NumberOfCases]*28,0) Or IIf([Grade] Like "*11P*",[NumberOfCases]*28,0) Or IIf([Grade] Like "*11S*",[NumberOfCases]*28,0)

I am getting results like "-1", but basically I need a formula that will multiply the "NumberOfCases" if it is a specific "Grade".

Thots?

 

[mrmarc]

I suggest to put the formula in the data source as a helper column instead, then use that helper column in the pivot table.

Thanks for the suggestion, but can you expand on this a bit?

 

[alansidman]

Try the following as I believe your syntax is incorrect:

IIf([Grade] Like "*SS1*" Or [Grade] Like "*SS2*"Or [Grade] Like "*11P*" Or [Grade] Like "*11S*",[NumberOfCases]*28,0)

 

[mrmarc]

Try the following as I believe your syntax is incorrect:

IIf([Grade] Like "*SS1*" Or [Grade] Like "*SS2*"Or [Grade] Like "*11P*" Or [Grade] Like "*11S*",[NumberOfCases]*28,0)

Thanks alan! that works.

Now what if I wanted to add a statement with another "true" result. So somehting like:
IIf([Grade] Like "*SS1*" Or [Grade] Like "*SS2*"Or [Grade] Like "*11P*" Or [Grade] Like "*11S*",[NumberOfCases]*28,0) And IIf([Grade] Like "*T1Costco*" Or [Grade] Like "*T2Costco*",[NumberOfCases]*27,0)

This is not working for me.

 

[alansidman]

i'm not understanding what you want from the expression you have show. Suggest you write out in simple English what your criteria is so that someone can convert your criteria to an expression that will work in Access.

 

[mrmarc]

thanks for the help guys.
maybe you can help my with this one:

I have created my own search popup form for a query. The query is in a subform on "form1" in a navigation form. I have coded the tab for "form1" to fire the search popup. It works, but my problem is that the "Enter Value Parameter" popup for the query is also firing. So I end up with first the "Enter Value Parameter" with "Forms!frmSearchGrowerPounds!cmbGrowers" firing and then my customized search popup form firing, so two pop ups.

I want to suppress the: "Enter Value Parameter" with "Forms!frmSearchGrowerPounds!cmbGrowers" so that only my customized search popup fires.

Any ideas?