Problem with IIF Statement

[eckert1961]

Hello,

I'm running Access 2003 and I'm having problems with an IIF statement that I have in one of my reports. Here is what I have so far.

=IIf((IsNull([Forms]![Attendance Form]![ThirdDate]) Or (Val([Field43])+7>=32),Null,Val([Field42])+7)

This statement works but not in the way that I need as I have 3 possible conditions.

I need to change this statement so that if the date field [ThirdDate] IsNull then Val([Field43])+7 is calculated and entered. If [ThirdDate] Isn't Null then Val([Field42])+7 is calculated and entered. Finally if Val([Field42])+7>=32 then a Null is entered into the field on the report.

Can this be accomplished with an IIF statement?

Thanks,
Chris

 

[pbaldy]

Yes, you just have to embed one within another, like this, where one IIf replaces the false argument of another:

IIf(Criteria, WhatToDoIfTrue, IIf(...))

 

[eckert1961]

Thanks Paul,

I tried changing the statement to the following.

=IIF(IsNull(Forms![Attendance Form]!ThirdDate), 
Val([Field43])+7,IIF(Val([Field43])+7>=32),Null,Val([Field42])+7)

Unfortunately I get the following error.

The expression you entered has a function containing the wrong number of arguments.

Any ideas of what's wrong?

Chris

 

[pbaldy]

IIF(Val([Field43])+7>=32),Null,Val([Field42])+7)

 

[eckert1961]

I believe my first attempt wasn't correct so I changed it to the following.

=IIF(IsNull(Forms![Attendance Form]!ThirdDate, 
Val([Field43])+7),IIF(Val([Field43])+7>=32,Null),
IIF(Forms![Attendance Form]!ThirdDate)>0,Val([Field42])+7))

Unfortunately, this still gave me the same error.

I then changed it to the following.

IIF(IsNull(Forms![Attendance Form]!ThirdDate), 
Val([Field43])+7,IIF(Val([Field43])+7>=32),Null,
IIF(Forms![Attendance Form]!ThirdDate)>0,Val([Field42])+7)

Now I get the following error.

Extra ) in query expression

I'm not sure which ) I need to delete to make it work.

Chris

 

[pbaldy]

Did you see my previous answer? I was going to add more, but accidentally clicked on "post reply". The red one is the bad one.

 

[eckert1961]

Hi Paul,

I have 3 criteria that I need to include in the statement. This is what I currently have.

IIF(IsNull(Forms![Attendance Form]!ThirdDate), 
Val([Field43])+7,IIF(Val([Field43])+7>=32,Null,
IIF(Forms![Attendance Form]!ThirdDate)>0,Val([Field42])+7)

I'm still getting the error of having too many ).

In the third criteria ThirdDate>0 I want to test if that field has a date; i.e. Isn't Null. Is my statement correct?

Additionally, this is entered in a Text Box, Control Source.

Thanks for your replies.

Chris

 

[Brianwarnock]

TRY

IIF(Not IsNull(Forms![Attendance Form]!ThirdDate), Val([Field42])+7,IIF(Val([Field43])+7>=32,Null,Val([Field43])+7))

Brian

 

[pbaldy]

FYI, you made the same mistake:

IIF(Forms![Attendance Form]!ThirdDate)>0,Val([Field42])+7)

Plus that one doesn't have a false argument. I'd also recommend not using spaces in your field names, and use a naming convention for your fields and controls. A week from now, you'll never remember what's in Field42.

 

[eckert1961]

Thanks to both Paul and Brian. I modified your suggestions and it works great. Here is the final statement.

=IIf(IsNull(Forms![Attendance Form]!ThirdDate) And 
Val([Field43])>=31,Val([Field43])+7,
IIf(Val([Field43])+7>=31,Null,Val([Field42])+7))

Feel free to recommend any additional changes.

Paul, I will also implement your recommendation of using a naming convention.

Thanks again.

Chris

 

[Brianwarnock]

I realise that I got my 42 and 43 mixed up and my code should be

IIF(IsNull(Forms![Attendance Form]!ThirdDate), Val([Field43])+7,IIF(Val([Field42])+7>=32,Null,Val([Field42])+7))


Your last code does not fulfill your logic of post1.
IIf(IsNull(Forms![Attendance Form]!ThirdDate) And Val([Field43])>=31 (shouldn't that be Val([Field43])+7>=31
is true you will enter Val([Field43])+7 even if it is greater than 31 when you want to enter Null, Er! except that in post1 it was 42 not 43 you were testing, as Paul said use meaningful names then this confusion wont arise.

Brian

 

[Brianwarnock]

Chris, the logic is easier to see in a block If as per VBA


If IsNull(Forms![Attendance Form]!ThirdDate) Then
enter=Val([Field43])+7
Elseif 'at this point we now know ThirdDate is not Null
Val([Field42])+7>=32 Then
enter=Null
Else
enter=Val([Field42])+7
end if

Then translate to the IIF form.

Brian

 

[eckert1961]

Hi Brian,

Thanks for the additional information. You're correct that, Val([Field43])>=31 should be Val([Field43])+7>=31.

I also implemented your revised code and it works great. I really appreciate your assistance. Take care.

Regards,
Chris