Complicated IIf Statement Problem
in [General]
|
Prev: Access sub form help neededSG 1400
Next: .dat file
From: magmike on 22 Mar 2010 15:52 On Mar 22, 11:58 am, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Mon, 22 Mar 2010 09:34:46 -0700 (PDT), magmike <magmi...(a)yahoo.com> wrote: > >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. > -- > > 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?
From: magmike on 22 Mar 2010 16:43 On Mar 22, 2:52 pm, magmike <magmi...(a)yahoo.com> wrote: > On Mar 22, 11:58 am, John W. Vinson > > > > > > <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > > On Mon, 22 Mar 2010 09:34:46 -0700 (PDT), magmike <magmi...(a)yahoo.com> wrote: > > >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. > > -- > > > 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],"")))))))))))))))))))))))))))))
From: John W. Vinson on 22 Mar 2010 16:46 On Mon, 22 Mar 2010 12:52:37 -0700 (PDT), magmike <magmike7(a)yahoo.com> wrote: >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)? -- John W. Vinson [MVP]
From: magmike on 22 Mar 2010 17:45 On Mar 22, 3:46 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Mon, 22 Mar 2010 12:52:37 -0700 (PDT), magmike <magmi...(a)yahoo.com> wrote: > >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)? > > -- > > John W. Vinson [MVP] 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?
From: John W. Vinson on 22 Mar 2010 18:18
On Mon, 22 Mar 2010 14:45:48 -0700 (PDT), magmike <magmike7(a)yahoo.com> wrote: >On Mar 22, 3:46�pm, John W. Vinson ><jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: >> On Mon, 22 Mar 2010 12:52:37 -0700 (PDT), magmike <magmi...(a)yahoo.com> wrote: >> >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)? >> >> -- >> >> � � � � � � �John W. Vinson [MVP] > >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. -- John W. Vinson [MVP] |