Complicated IIf Statement Problem
in [General]
|
Prev: Access sub form help neededSG 1400
Next: .dat file
From: Douglas J. Steele on 22 Mar 2010 10:10 =NameOfFunction(arg1, arg2, ...) The equal sign needs to be there. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "magmike" <magmike7(a)yahoo.com> wrote in message news:0b0cc665-e2bf-4d69-ae31-627aa30679cd(a)r1g2000yqj.googlegroups.com... On Mar 22, 5:12 am, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > 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!) > > "magmike" <magmi...(a)yahoo.com> wrote in message > > news:ab669e02-69c9-4853-9149-9ad2d85e5e7a(a)k17g2000yqb.googlegroups.com... > On Mar 21, 6:10 am, "Douglas J. Steele" > > > > > > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > > Why not create a function and use that function as the ControlSource, > > rather > > than trying to jam it all into a massive IIf statement? > > > -- > > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele > > (no e-mails, please!) > > > "magmike" <magmi...(a)yahoo.com> wrote in message > > >news:54ed10b5-5c4c-4fe9-bb4f-ac9a7f63db43(a)y17g2000yqd.googlegroups.com... > > > > 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 > > 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?
From: magmike on 22 Mar 2010 12:34 On Mar 22, 9:10 am, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > =NameOfFunction(arg1, arg2, ...) > > The equal sign needs to be there. > > -- > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele > (no e-mails, please!) > > "magmike" <magmi...(a)yahoo.com> wrote in message > > news:0b0cc665-e2bf-4d69-ae31-627aa30679cd(a)r1g2000yqj.googlegroups.com... > On Mar 22, 5:12 am, "Douglas J. Steele" > > > > > > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > > 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!) > > > "magmike" <magmi...(a)yahoo.com> wrote in message > > >news:ab669e02-69c9-4853-9149-9ad2d85e5e7a(a)k17g2000yqb.googlegroups.com.... > > On Mar 21, 6:10 am, "Douglas J. Steele" > > > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > > > Why not create a function and use that function as the ControlSource, > > > rather > > > than trying to jam it all into a massive IIf statement? > > > > -- > > > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele > > > (no e-mails, please!) > > > > "magmike" <magmi...(a)yahoo.com> wrote in message > > > >news:54ed10b5-5c4c-4fe9-bb4f-ac9a7f63db43(a)y17g2000yqd.googlegroups.com.... > > > > > 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 > > > 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?- 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.
From: Douglas J. Steele on 22 Mar 2010 12:56 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!) "magmike" <magmike7(a)yahoo.com> wrote in message news:76e52f67-7699-425d-8fc2-8cf60f720da2(a)l25g2000yqd.googlegroups.com... On Mar 22, 9:10 am, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > =NameOfFunction(arg1, arg2, ...) > > The equal sign needs to be there. > > -- > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele > (no e-mails, please!) > > "magmike" <magmi...(a)yahoo.com> wrote in message > > news:0b0cc665-e2bf-4d69-ae31-627aa30679cd(a)r1g2000yqj.googlegroups.com... > On Mar 22, 5:12 am, "Douglas J. Steele" > > > > > > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > > 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!) > > > "magmike" <magmi...(a)yahoo.com> wrote in message > > >news:ab669e02-69c9-4853-9149-9ad2d85e5e7a(a)k17g2000yqb.googlegroups.com... > > On Mar 21, 6:10 am, "Douglas J. Steele" > > > <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > > > Why not create a function and use that function as the ControlSource, > > > rather > > > than trying to jam it all into a massive IIf statement? > > > > -- > > > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele > > > (no e-mails, please!) > > > > "magmike" <magmi...(a)yahoo.com> wrote in message > > > >news:54ed10b5-5c4c-4fe9-bb4f-ac9a7f63db43(a)y17g2000yqd.googlegroups.com... > > > > > 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 > > > 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?- 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.
From: John W. Vinson on 22 Mar 2010 12:58 On Mon, 22 Mar 2010 09:34:46 -0700 (PDT), magmike <magmike7(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]
From: Douglas J. Steele on 22 Mar 2010 14:19
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:j48fq5p05md48off3cfl4d7bdtltup71dg(a)4ax.com... > > 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. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) |