Writing Multiple IIF Function

I

Imran Ghani

Hi! I am using access 2007 and trying to code in VBA whereby I am using the
IIF funtion and trying to calculate a range of values returned by a datediff
funtion but am not able to calculate the value. Its giving value error. I am
writing my code as under:
Me!sixMonths = IIf([status] = "partial settlement", 0, IIf([status] = "fully
settled", 0, IIf(datdif between 90 and 180, [balamt], 0)))
I'd be greatful if someone could help me to resolve the problem. Thanks in
advance.
 
J

Jack Leach

Generally if I run into a situation where I have to nest any conditional
inside an Iif function, I'll take a looke at rewriting it as an If/Then/Else
statement(s).

Me!sixMonths = IIf([status] = "partial settlement", 0, IIf([status] = "fully
settled", 0, IIf(datdif between 90 and 180, [balamt], 0)))
Click to expand...

Try something like this:

If ([status] = "partial settlement") OR ([status] = "fully settled") Then
Me!sixMonths = 0
Else
Me!sixMonths = Iif((DateDiff(...) > 89) And (DateDiff(...) < 181),
[balamt], 0)
End If



(DateDiff(...) > 89) And (DateDiff(...) < 181),

The DateDiff function requires some arguments where the "..." is. I'm not
sure what the value is that you are trying to compare (you'll most likely
refer to a field or control), but you'll have to take a look at the help file
for DateDiff() and see what you can come up with. I'm no expert when it
comes to working with dates (I think I've only had to use the datediff
function once or twice as of yet and I don't remember the syntax offhand).

Rewriting the If/Then structure as I did is not required, your nested Iif's
should work fine if you get the datediff straightened around, but it does
make it a whole lot easier to read.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Imran Ghani said:
Hi! I am using access 2007 and trying to code in VBA whereby I am using the
IIF funtion and trying to calculate a range of values returned by a datediff
funtion but am not able to calculate the value. Its giving value error. I am
writing my code as under:
Me!sixMonths = IIf([status] = "partial settlement", 0, IIf([status] = "fully
settled", 0, IIf(datdif between 90 and 180, [balamt], 0)))
I'd be greatful if someone could help me to resolve the problem. Thanks in
advance.
Click to expand...
 
K

KC-Mass

Me!sixMonths = IIf(([status] = "partial settlement" OR [status] = "fully
Settled" OR datdif NOT BETWEEN 90 and 180), 0, [balamt])

Should work assuming that "datdif" is defined as an interger or long
variable and is assigned
a value before the above statement executes.

Regards

Kevin
 
I

Imran Ghani

Thanks for a prompt and helpful advice.

Jack Leach said:
Generally if I run into a situation where I have to nest any conditional
inside an Iif function, I'll take a looke at rewriting it as an If/Then/Else
statement(s).

Me!sixMonths = IIf([status] = "partial settlement", 0, IIf([status] = "fully
settled", 0, IIf(datdif between 90 and 180, [balamt], 0)))
Click to expand...

Try something like this:

If ([status] = "partial settlement") OR ([status] = "fully settled") Then
Me!sixMonths = 0
Else
Me!sixMonths = Iif((DateDiff(...) > 89) And (DateDiff(...) < 181),
[balamt], 0)
End If



(DateDiff(...) > 89) And (DateDiff(...) < 181),

The DateDiff function requires some arguments where the "..." is. I'm not
sure what the value is that you are trying to compare (you'll most likely
refer to a field or control), but you'll have to take a look at the help file
for DateDiff() and see what you can come up with. I'm no expert when it
comes to working with dates (I think I've only had to use the datediff
function once or twice as of yet and I don't remember the syntax offhand).

Rewriting the If/Then structure as I did is not required, your nested Iif's
should work fine if you get the datediff straightened around, but it does
make it a whole lot easier to read.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Imran Ghani said:
Hi! I am using access 2007 and trying to code in VBA whereby I am using the
IIF funtion and trying to calculate a range of values returned by a datediff
funtion but am not able to calculate the value. Its giving value error. I am
writing my code as under:
Me!sixMonths = IIf([status] = "partial settlement", 0, IIf([status] = "fully
settled", 0, IIf(datdif between 90 and 180, [balamt], 0)))
I'd be greatful if someone could help me to resolve the problem. Thanks in
advance.
Click to expand...
Click to expand...
 
J

JimBurke via AccessMonster.com

I'm pretty sure that Between is an SQL keyword, not a VBA keyword. I also
agree that an If-Then-Else would be the easiest thing to use. Assuming I have
your logic right I'd use:

If status = "partial settlement" or status = "fully settled" then
sixMonths = 0
Else
if datdif >= 90 and datedif <= 180 then
sixmonths = balamt
Else
sixmonths = 0
End If
End If

You could actually shorten the code even more, but it's not as easily
readable:

If not (status = "partial settlement" or status = "fully settled") and _
datdif >= 90 and datdif <= 180 then
sixmonths = balamt
Else
sixmonths = 0
End If

Again, this assumes I'm interpreting your logic correctly!

Imran said:
Hi! I am using access 2007 and trying to code in VBA whereby I am using the
IIF funtion and trying to calculate a range of values returned by a datediff
funtion but am not able to calculate the value. Its giving value error. I am
writing my code as under:
Me!sixMonths = IIf([status] = "partial settlement", 0, IIf([status] = "fully
settled", 0, IIf(datdif between 90 and 180, [balamt], 0)))
I'd be greatful if someone could help me to resolve the problem. Thanks in
advance.
Click to expand...
 
Top