Nested IIF problem

  • Thread starter Thread starter thebrat26
  • Start date Start date
T

thebrat26

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
 
thebrat26 said:
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
Click to expand...


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.
 
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
Click to expand...

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?
 
Back
Top