Complicated IIf Statement Problem

[magmike]

This IIf statement is in an unbound control on a report. When I paste
it into the Control Source under the unbound control's properties - I
get an error saying the expression is too complicated. I've double
checked it, and it is crafted correctly. I'm guessing it is too long.
Is there a maximum amount of IIf checks?

Just a little background - There are 29 different possible fields from
a query that I might want to populate this control with, depending on
the value of another field. Unfortunately, I am stuck working with a
badly built database and trying to make this work in spite of it. So,
my control source answer is a 29 step IIf statement. Is there a work
around on this?

Thanks in advance for your help!

magmike

 

[Douglas J. Steele]

Why not create a function and use that function as the ControlSource, rather
than trying to jam it all into a massive IIf statement?

 

[magmike]

Why not create a function and use that function as the ControlSource, rather
than trying to jam it all into a massive IIf statement?

I didn't realize I could do a function in a report. How would I do
that?

 

[Douglas J. Steele]

Reports can have modules associated with them, or you can create the
function in a stand-alone module.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Why not create a function and use that function as the ControlSource,
rather
than trying to jam it all into a massive IIf statement?

I didn't realize I could do a function in a report. How would I do
that?

 

[magmike]

Reports can have modules associated with them, or you can create the
function in a stand-alone module.

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
(no e-mails, please!)




I didn't realize I could do a function in a report. How would I do
that?

How would I reference the function in the Control Source?

 

[Douglas J. Steele]

=NameOfFunction(arg1, arg2, ...)

The equal sign needs to be there.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Reports can have modules associated with them, or you can create the
function in a stand-alone module.

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
(no e-mails, please!)




I didn't realize I could do a function in a report. How would I do
that?

How would I reference the function in the Control Source?

 

[magmike]

=NameOfFunction(arg1, arg2, ...)

The equal sign needs to be there.

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
(no e-mails, please!)





How would I reference the function in the Control Source?- Hide quoted text -

- Show quoted text -

I hope this doesn't come off as stupid - but I am just a hack that
does what I need to get by, but, how would I link the module to the
report? And would a function be the same as an event like I do on a
form?

I don't expect you to do this for me - but if you know of a good
resource that I can study up with - I'm rather new to the module and
to reports.

 

[Douglas J. Steele]

Open the report in Design view.

Go to the Properties window and set the Has Module property to Yes (look on
the Other tab).

Go into the VB Editor (Alt-F11)

Ensure the Project Explorer window is open (Using Ctrl-R to display it if it
isn't)

You should now find an entry for Report_<NameOfYourReport> under Microsoft
Office Access Class Objects in the explorer. Double-click on it to get into
the module.

Add your function there.



--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

=NameOfFunction(arg1, arg2, ...)

The equal sign needs to be there.

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
(no e-mails, please!)





How would I reference the function in the Control Source?- Hide quoted
text -

- Show quoted text -

I hope this doesn't come off as stupid - but I am just a hack that
does what I need to get by, but, how would I link the module to the
report? And would a function be the same as an event like I do on a
form?

I don't expect you to do this for me - but if you know of a good
resource that I can study up with - I'm rather new to the module and
to reports.

 

[John W. Vinson]

I hope this doesn't come off as stupid - but I am just a hack that
does what I need to get by, but, how would I link the module to the
report? And would a function be the same as an event like I do on a
form?

You can create a function either in the Form's Module, or in a new Module (on
the modules tab in the database window). The function can have any name you
like (well, not the same as the name of any stored Module, no blanks or
special characters in the name, etc.).

So let's say you create a function

Public Function MyNeatFunction(OneArg As String, AnotherArg As Long) As Long
<do a bunch of stuff with the arguments or with form references>
MyNeatFunction = <some expression>
End Function

Then, you can use the function in two ways:

1. If it's in a Module rather than in the Form's Module, you can put

NewLabel: MyNeatFunction([textfield], 31)

2. On the Form which has the function in its module, you can set the Control
Source of a textbox to

=MyNeatFunction("Active", [NameOfANumberControl])

The name of the module is irrelevant, and isn't needed in the use of the
function.

 

[Douglas J. Steele]

The name of the module is irrelevant

Not quite, John. The name of the module must be unique: it cannot be the
same as the name of any function (or sub) contained in the project.

 

[magmike]

I hope this doesn't come off as stupid - but I am just a hack that
does what I need to get by, but, how would I link the module to the
report? And would a function be the same as an event like I do on a
form?

You can create a function either in the Form's Module, or in a new Module(on
the modules tab in the database window). The function can have any name you
like (well, not the same as the name of any stored Module, no blanks or
special characters in the name, etc.).

So let's say you create a function

Public Function MyNeatFunction(OneArg As String, AnotherArg As Long) As Long
<do a bunch of stuff with the arguments or with form references>
MyNeatFunction = <some expression>
End Function

Then, you can use the function in two ways:

1. If it's in a Module rather than in the Form's Module, you can put

NewLabel: MyNeatFunction([textfield], 31)

2. On the Form which has the function in its module, you can set the Control
Source of a textbox to

=MyNeatFunction("Active", [NameOfANumberControl])

The name of the module is irrelevant, and isn't needed in the use of the
function.

So, if I were simply trying to do a series of If/Then checks, how
would I write my function to generate that value when the Control
Source calls for that function?

 

[magmike]

You can create a function either in the Form's Module, or in a new Module (on
the modules tab in the database window). The function can have any nameyou
like (well, not the same as the name of any stored Module, no blanks or
special characters in the name, etc.).

So let's say you create a function

Public Function MyNeatFunction(OneArg As String, AnotherArg As Long) AsLong
<do a bunch of stuff with the arguments or with form references>
MyNeatFunction = <some expression>
End Function

Then, you can use the function in two ways:

1. If it's in a Module rather than in the Form's Module, you can put

NewLabel: MyNeatFunction([textfield], 31)

2. On the Form which has the function in its module, you can set the Control
Source of a textbox to

=MyNeatFunction("Active", [NameOfANumberControl])

The name of the module is irrelevant, and isn't needed in the use of the
function.
--

             John W. Vinson [MVP]

So, if I were simply trying to do a series of If/Then checks, how
would I write my function to generate that value when the Control
Source calls for that function?- Hide quoted text -

- Show quoted text -

PS: Here is the IIf statement I was trying to use in an unbound
control:

=IIf([Deduction]="Accident",[ACC DED],IIf([Deduction]="Boone Union",
[MED DED],IIf([Deduction]="Cancer Insurance",[CANC
DED],IIf([Deduction]="Carter Union",[MED
DED],IIf([Deduction]="Colonial Life Insurance",[COL UL
DED],IIf([Deduction]="Critical Illness",[CI
DED],IIf([Deduction]="Dental Pro 1",[DEN DED],IIf([Deduction]="Dental
Pro 2",[DEN DED],IIf([Deduction]="Dependent Life",[DEP LF
DED],IIf([Deduction]="Group Term Life (X2)DMS",[BU LF
DED],IIf([Deduction]="Group Term Life(X1)",[BU LF
DED],IIf([Deduction]="Group Term Life(X1)DMS",[BU LF
DED],IIf([Deduction]="Group Term Life(X2)",[BU LF
DED],IIf([Deduction]="High Deductible Health Plan",[CORP MED
DED],IIf([Deduction]="Kentucky Non-Union Medical",[MED
DED],IIf([Deduction]="Med Sup (2yrs+)",[P3 MS
DED],IIf([Deduction]="Med Sup 3 (6-24mo.)",[P3 MS
DED],IIf([Deduction]="Med Supp 3 (3-6mo.)",[P3 MS
DED],IIf([Deduction]="Medical Bridge",[COL MED BR
DED],IIf([Deduction]="Medical Plan 1",[CORP MED
DED],IIf([Deduction]="Medical Plan 2",[MED DED],IIf([Deduction]="PLAN
1 80% HDHP",[MED DED],IIf([Deduction]="Plan 1 Dental",[CORP DEN
DED],IIf([Deduction]="PLAN 2 80% HDHP",[MED DED],IIf([Deduction]="Plan
3 KY 80% HDHP",[MED DED],IIf([Deduction]="Plan 3 RX",[P3 RX
DED],IIf([Deduction]="Plan 3 RX Generic",[P3 RX
DED],IIf([Deduction]="Pre Tax Disability",[STD
DED],IIf([Deduction]="Vision Plan",[VIS
DED],"")))))))))))))))))))))))))))))

 

[John W. Vinson]

So, if I were simply trying to do a series of If/Then checks, how
would I write my function to generate that value when the Control
Source calls for that function?

Your previous message has dropped off my board... could you describe just what
you want to check, and what's the context (a query, a form, or what)?

 

[magmike]

Your previous message has dropped off my board... could you describe justwhat
you want to check, and what's the context (a query, a form, or what)?

I have developed a query that compares two tables. The first table has
one record per employee which contains a field each for 18 possible
payroll deductions. The second table is from our clients payroll data.
There are multiple records per employee, one each for each deduction
coming out of their paycheck. The client has 29 different possible
deductions, each fitting into one of our 18 deduction fields.

I have successfully built a query that does a comparison and only
lists discrepancies between the two. For example, the query may show
that Bob Smith has a payroll deduction for his Carter Union plan (fits
in our [MED DED] field) of 86.92, while our database may only show a
deduction of 34.83 in our MED DED field.

Now I am creating a report that will show these discrepancies grouped
by location (there are 57) so they can be investigated. In the above
example, the most likely option is, that at open enrollment, Bob chose
to add his wife to the plan, resulting in the increased deduction, but
then our office was not notified of the change, which is why our data
is different. If we were not notified, then his wife has NOT been
enrolled into the health plan.

Because of the differences in the way the two tables are constructed
(I have lobbied for a change in the database's design, but have been
denied), for my query to work correctly, comparing all these fields in
one location, each record will show a column for each possible
deduction. Therefore, I need to craft the deduction field in the
report to show the deduction for the matching deduction title, such as
"Carter Union". Therefore, I thought I could use an unbound field and
use a 29 deep IIf statement- but I'm learning that is too complex of
an argument for the Control Source. So now from direction in this
newsgroup, I am trying to figure out how to craft a function I can
refer to in the Control Source of that report field.

Did I answer your question?

 

[John W. Vinson]

I have developed a query that compares two tables. The first table has
one record per employee which contains a field each for 18 possible
payroll deductions.

So when you change or add a new deduction, you redesign your table, all your
queries, all your forms...? If you have a Many (employees) to Many
(deductions) relationship, the better design would be a table for Employees, a
table for Deductions (18 rows today... 20 next month <g>), and a third table
related one to many to both of them. Given this normalized design, a very
simple totals query could sum up all the deductions for an employee.

The second table is from our clients payroll data.
There are multiple records per employee, one each for each deduction
coming out of their paycheck. The client has 29 different possible
deductions, each fitting into one of our 18 deduction fields.

And a very simple join between this (proper!) table and the normalized version
of yours would require no IIF, no code, no complications at all.

I have successfully built a query that does a comparison and only
lists discrepancies between the two. For example, the query may show
that Bob Smith has a payroll deduction for his Carter Union plan (fits
in our [MED DED] field) of 86.92, while our database may only show a
deduction of 34.83 in our MED DED field.

A "Normalizing Union Query" may be your best bet:

SELECT EmployeeID, Location, "MED DED" AS DeductionTYpe, [MED DED] AS
DeductionAmount FROM firsttable
WHERE [MED DED] IS NOT NULL
UNION ALL
SELECT EmployeeID, Location, "UNION DUES", [UNI DUE]
FROM firsttable
WHERE [UNI DUE] IS NOT NULL
UNION ALL
<etc etc through all 18 fields>


This will create a "tall thin" table view based on your data.

You can then join this table to your master table on EmployeeID and the
deduction type, with a criterion to select only records where the amount is
discrepant.

Now I am creating a report that will show these discrepancies grouped
by location (there are 57) so they can be investigated. In the above
example, the most likely option is, that at open enrollment, Bob chose
to add his wife to the plan, resulting in the increased deduction, but
then our office was not notified of the change, which is why our data
is different. If we were not notified, then his wife has NOT been
enrolled into the health plan.

Because of the differences in the way the two tables are constructed
(I have lobbied for a change in the database's design, but have been
denied), for my query to work correctly, comparing all these fields in
one location, each record will show a column for each possible
deduction. Therefore, I need to craft the deduction field in the
report to show the deduction for the matching deduction title, such as
"Carter Union". Therefore, I thought I could use an unbound field and
use a 29 deep IIf statement- but I'm learning that is too complex of
an argument for the Control Source. So now from direction in this
newsgroup, I am trying to figure out how to craft a function I can
refer to in the Control Source of that report field.

Did I answer your question?

I think so; did my reply help?

If you in fact need the VBA function to replicate your complex IIF, I'll try
to help come up with one.

 

[magmike]

So when you change or add a new deduction, you redesign your table, all your

queries, all your forms...? If you have a Many (employees) to Many
(deductions) relationship, the better design would be a table for Employees, a
table for Deductions (18 rows today... 20 next month <g>), and a third table
related one to many to both of them. Given this normalized design, a very
simple totals query could sum up all the deductions for an employee.

I agree whole heartedly. That is how I would have designed the
database. But they will not let me redesign the database out of fear.
They just want me to make the comparison portion of it possible. I
know it is not perfect, but If I can figure out how to write the
function, then I am there. Again, the function just needs to figure
out what the deduction title is, then refer to the proper deduction
amount field, and then I am in. I'm hoping to win them over on the
rest eventually.

BTW, the deductions will not change from 18 - that much is in stone.
All the other possible deductions will fit into one of those 18
categories.

A "Normalizing Union Query" may be your best bet:

Would this work with the current "bad" design - or is this if I were
able to get them to allow me to redesign their tables?

If you in fact need the VBA function to replicate your complex IIF, I'll try
to help come up with one.

Yes, please - for now, that is the option I am stuck with. After this
redneck version is complete, I may try a redesign in my spare time for
the good of the company.

Thanks, for all your help.

magmike

 

[John W. Vinson]

Would this work with the current "bad" design - or is this if I were
able to get them to allow me to redesign their tables?

It should work with the current design (it may be pretty slow to run), but
yes, it will recast the current wide-flat structure into a tall-thin.

Yes, please - for now, that is the option I am stuck with. After this
redneck version is complete, I may try a redesign in my spare time for
the good of the company.

Post some of your fieldnames and the calculation that you want to do.

 

[magmike]

It should work with the current design (it may be pretty slow to run), but
yes, it will recast the current wide-flat structure into a tall-thin.



Post some of your fieldnames and the calculation that you want to do.

No calculations neccessary. This function is not about calculating -
the query has already compared. I just need to determine which
deduction amount to display in the unbound field on my REPORT. I wrote
an IIf statement, and if I could accomplish that in a function, that
would be what I need. The IIf statement includes all the field names:

=IIf([Deduction]="Accident",[ACC DED],IIf([Deduction]="Boone Union",
[MED DED],IIf([Deduction]="Cancer Insurance",[CANC
DED],IIf([Deduction]="Carter Union",[MED
DED],IIf([Deduction]="Colonial Life Insurance",[COL UL
DED],IIf([Deduction]="Critical Illness",[CI
DED],IIf([Deduction]="Dental Pro 1",[DEN DED],IIf([Deduction]="Dental
Pro 2",[DEN DED],IIf([Deduction]="Dependent Life",[DEP LF
DED],IIf([Deduction]="Group Term Life (X2)DMS",[BU LF
DED],IIf([Deduction]="Group Term Life(X1)",[BU LF
DED],IIf([Deduction]="Group Term Life(X1)DMS",[BU LF
DED],IIf([Deduction]="Group Term Life(X2)",[BU LF
DED],IIf([Deduction]="High Deductible Health Plan",[CORP MED
DED],IIf([Deduction]="Kentucky Non-Union Medical",[MED
DED],IIf([Deduction]="Med Sup (2yrs+)",[P3 MS
DED],IIf([Deduction]="Med Sup 3 (6-24mo.)",[P3 MS
DED],IIf([Deduction]="Med Supp 3 (3-6mo.)",[P3 MS
DED],IIf([Deduction]="Medical Bridge",[COL MED BR
DED],IIf([Deduction]="Medical Plan 1",[CORP MED
DED],IIf([Deduction]="Medical Plan 2",[MED DED],IIf([Deduction]="PLAN
1 80% HDHP",[MED DED],IIf([Deduction]="Plan 1 Dental",[CORP DEN
DED],IIf([Deduction]="PLAN 2 80% HDHP",[MED
DED],IIf([Deduction]="Plan
3 KY 80% HDHP",[MED DED],IIf([Deduction]="Plan 3 RX",[P3 RX
DED],IIf([Deduction]="Plan 3 RX Generic",[P3 RX
DED],IIf([Deduction]="Pre Tax Disability",[STD
DED],IIf([Deduction]="Vision Plan",[VIS
DED],"")))))))))))))))))))))))))))))

 

[John W. Vinson]

No calculations neccessary. This function is not about calculating -
the query has already compared. I just need to determine which
deduction amount to display in the unbound field on my REPORT. I wrote
an IIf statement, and if I could accomplish that in a function, that
would be what I need. The IIf statement includes all the field names:

=IIf([Deduction]="Accident",[ACC DED],IIf([Deduction]="Boone Union",
[MED DED],IIf([Deduction]="Cancer Insurance",[CANC
DED],IIf([Deduction]="Carter Union",[MED
DED],IIf([Deduction]="Colonial Life Insurance",[COL UL
DED],IIf([Deduction]="Critical Illness",[CI
DED],IIf([Deduction]="Dental Pro 1",[DEN DED],IIf([Deduction]="Dental
Pro 2",[DEN DED],IIf([Deduction]="Dependent Life",[DEP LF
DED],IIf([Deduction]="Group Term Life (X2)DMS",[BU LF
DED],IIf([Deduction]="Group Term Life(X1)",[BU LF
DED],IIf([Deduction]="Group Term Life(X1)DMS",[BU LF
DED],IIf([Deduction]="Group Term Life(X2)",[BU LF

Ok... a VBA-code solution (not the best solution, but what you're asking for)
might be to put the following function in the *form's* Module.

Private Function DeductionAmount(strDeduction As String) As Variant
Select Case Deduction
Case "Accident"
DeductionAmount = Me![ACC DED]
Case "Cancer Insurance"
DeductionAmount = Me![CANC DED]
Case "Carter Union"
DeductionAmount = Me![MED DED]
Case "Colonial Life Insurance"
DeductionAmount = Me![COL UL DED]
....
Case "Group Term Life (X2)DMS", "Group Term Life(X1)", _
"Group Term Life(X1)DMS", "Group Term Life(X2)"
DeductionAmount = Me![BU LF DED]
....
<go through all the cases>
Case Else
DeductionAmount = Null
End Select
End Function

A table driven solution with a table of deduction types and the corresponding
fieldname would be better and easier to maintain.

 

[magmike]

Ok... a VBA-code solution (not the best solution, but what you're asking for)

might be to put the following function in the *form's* Module.

Would this not work in the report's Module?