If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested IIF problem
Hi
[PayReceived] is a tick box on a form from a subscriptions table. [DateDifference] calculates the number of dates between the [DueDate] and Today’s Date to work out if the subscription payment is overdue by 30 days or more and [MembStatus] holds a string depending on how many days overdue the payment is. I am having problems with the nested IIf statement which should calculate [MembStatus] and no matter what I do I either get a syntax error, too many brackets error or an error about commas – and I’ve been trawling through the questions and answers on this subject and tried this so many different ways but can't seem to get it right. I’ve had problems with even the simplest calculations which then suddenly seems to update and expressions that did not work previously suddenly work so I’m beginning to get a little paranoid – is anyone else having trouble with Access? I was using Vista but upgraded to Windows 7 because of other problems I was having with other programs not updating (Windows Update and AVG to name 2). This is what I’m trying to accomplish: [MembStatus] = If PayReceived = False AND DateDifference is between 30 and 60 = “Active” DateDifference is 60 but =90 = “Pending” DateDifference is 90 but =120 = “Suspended” DateDifference is 120 but =150 = “Inactive” DateDifference is 150 = “CLOSED” Else “Active” Thank you in anticipation TheBrat |
#2
|
|||
|
|||
Nested IIF problem
"thebrat26" wrote in message
... Hi [PayReceived] is a tick box on a form from a subscriptions table. [DateDifference] calculates the number of dates between the [DueDate] and Today’s Date to work out if the subscription payment is overdue by 30 days or more and [MembStatus] holds a string depending on how many days overdue the payment is. I am having problems with the nested IIf statement which should calculate [MembStatus] and no matter what I do I either get a syntax error, too many brackets error or an error about commas – and I’ve been trawling through the questions and answers on this subject and tried this so many different ways but can't seem to get it right. I’ve had problems with even the simplest calculations which then suddenly seems to update and expressions that did not work previously suddenly work so I’m beginning to get a little paranoid – is anyone else having trouble with Access? I was using Vista but upgraded to Windows 7 because of other problems I was having with other programs not updating (Windows Update and AVG to name 2). This is what I’m trying to accomplish: [MembStatus] = If PayReceived = False AND DateDifference is between 30 and 60 = “Active” DateDifference is 60 but =90 = “Pending” DateDifference is 90 but =120 = “Suspended” DateDifference is 120 but =150 = “Inactive” DateDifference is 150 = “CLOSED” Else “Active” Thank you in anticipation TheBrat Without seeing examples of the IIF expressions you've been trying to use, I can't comment on what you may hve been doing wrong in them. I probably wouldn't use a nested IIf() for this, though, since the Switch() function can represent the case structure more precisely: =Switch([PayReceived]=True, "Active", [DateDifference]=60, "Active", [DateDifference]=90, "Pending", [DateDifference]=120, "Suspended", [DateDifference]=150, "Inactive", [DateDifference]150, "CLOSED", True, "Active") I broke that onto separate lines to clarify the cases, but it would all be on one line in the ControlSource of [MembStatus]. You didn't explicitly say what should be shown if DateDifference is 30, so but your posted logic implies that it should be "Active", so I coded that. Note that Switch is a VBA function, so to use it like this, VBA must be enabled in your database. If you're using Access 2007 or later, that means the database must either be in a trusted location, or you must specifically enable the VBA. It's possible that Jet Sandbox Mode must also be disabled; I'm not sure off the top of my head. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#3
|
|||
|
|||
Nested IIF problem
On Sun, 31 Jan 2010 08:34:01 -0800, thebrat26
wrote: Hi [PayReceived] is a tick box on a form from a subscriptions table. [DateDifference] calculates the number of dates between the [DueDate] and Todays Date to work out if the subscription payment is overdue by 30 days or more and [MembStatus] holds a string depending on how many days overdue the payment is. I am having problems with the nested IIf statement which should calculate [MembStatus] and no matter what I do I either get a syntax error, too many brackets error or an error about commas and Ive been trawling through the questions and answers on this subject and tried this so many different ways but can't seem to get it right. Ive had problems with even the simplest calculations which then suddenly seems to update and expressions that did not work previously suddenly work so Im beginning to get a little paranoid is anyone else having trouble with Access? I was using Vista but upgraded to Windows 7 because of other problems I was having with other programs not updating (Windows Update and AVG to name 2). This is what Im trying to accomplish: [MembStatus] = If PayReceived = False AND DateDifference is between 30 and 60 = Active DateDifference is 60 but =90 = Pending DateDifference is 90 but =120 = Suspended DateDifference is 120 but =150 = Inactive DateDifference is 150 = CLOSED Else Active Thank you in anticipation Dirk's Switch() suggestion is certainly better than a snarky mass of nested IIF's; but you may want to consider going a step further, and using a table-driven solution instead of embedding this business rule in code. You could have a small Status table with fields Age and Status: 30 Active 60 Pending 90 Suspended 120 Inactive 150 CLOSED 0 whatever you want for date difference less than 30 You could then use a non-equijoin Query or DLookUp to find the Status for a given date. The details would depend on the context; I hope that DateDifference isn't stored but is instead calculated from some date! What's the rest of the query? -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|