Writing Multiple IIF Function

  • Thread starter Thread starter Imran Ghani
  • Start date Start date
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.
 
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...
 
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
 
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...
 
Back
Top