IIf function with fewer conditions in Access
Hi All,
I'm having a hard time with a formula that I'm writing in Access, I want to display a word if 3 conditions are filled together, and if not another 3 conditions need to be filled but it's not working, any idea?
Test4: IIf((([Tbl_Formatted_Calls]![TIME]+[Tbl_Formatted_Calls]![Duration_Time_Format]>#23:59:59#) And ([Tbl_Dates]![Day+1]='Ban') And ([Tbl_Dates]![Day]='Mon' Or 'Tue' Or 'Wed' Or 'Thu' Or 'Fri')),'Yes',IIf((([Tbl_Formatted_Calls]![TIME]+[Tbl_Formatted_Calls]![Duration_Time_Format]>#23:59:59#) And ([Tbl_Dates]![Day+1]='Sat') And ([Tbl_Dates]![Day]='Mon' Or 'Tue' Or 'Wed' Or 'Thu' Or 'Fri')),'Saty',IIf((([Tbl_Formatted_Calls]![TIME]+[Tbl_Formatted_Calls]![Duration_Time_Format]>#23:59:59#) And ([Tbl_Dates]![Day+1]='Mon' Or 'Tue' Or 'Wed' Or 'Thu' Or 'Fri') And ([Tbl_Dates]![Day]='Sun')),'Suny',IIf((([Tbl_Formatted_Calls]![TIME]+[Tbl_Formatted_Calls]![Duration_Time_Format]>#23:59:59#) And ([Tbl_Dates]![Day+1]='Mon' Or 'Tue' Or 'Wed' Or 'Thu' Or 'Fri') And ([Tbl_Dates]![Day]='Ban')),'Bany','Bann'))))
Thanks in Advance
[1383 byte] By [
edtrvl] at [2007-11-11 10:09:01]
# 1 Re: IIf function with fewer conditions in Access
Try This Version:
Test4: IIf(([Tbl_Formatted_Calls]![TIME]+[Tbl_Formatted_Calls]![Duration_Time_Format]>#23:59:59#), IIf(([Tbl_Dates]![Day]='Mon' Or 'Tue' Or 'Wed' Or 'Thu' Or 'Fri'), IIF([Tbl_Dates]![Day+1]='Ban','Yes',IIf([Tbl_Dates]![Day+1]='Sat','Saty', 'Bann')), IIf(([Tbl_Dates]![Day+1]='Mon' Or 'Tue' Or 'Wed' Or 'Thu' Or 'Fri'), IIf([Tbl_Dates]![Day]='Sun','Suny',IIf([Tbl_Dates]![Day]='Ban','Bany','Bann')),'Bann')),'Bann')
# 2 Re: IIf function with fewer conditions in Access
Thanks a mil Ron,
I thought of doing so and seems to be the right way to do it.
The only thing is that the formula above is only one third of the whole... and to keep it simple tried to group them with the "And". I'll get there no worries, thanks again
Ed
edtrvl at 2007-11-11 23:44:31 >
# 3 Re: IIf function with fewer conditions in Access
The logic behind your IIF is obviously complex.
For readability and error-correction facility, in these situations I am often moved to consider trapping values from the database, and then using a SELECT CASE or IF-THEN-ELSE construct.
'Trap Values
aVar = database value 1
aVar2 = database value 2
'Logical construct
'Select CASE or IF-Then-Else framework
'[using a SELECT tree, here for demonstration]
SELECT CASE var 'different languages use different syntax...
case is = 'this'
VarResult = 'something'
case is = 'that'
VarResult = 'something else'
case is = 'yet another'
SELECT CASE ' embedded logic for this case
...
END CASE
case is =
...
END CASE
'use VarResult in the field being displayed, if a form/report...
' public VarResult might be required
To my taste, using this method allows a think thru of the logic which is generating the result, and is far simpler to modify, later.
# 4 Re: IIf function with fewer conditions in Access
That is a very good point, with logic this complex it would be easier to write a VBA function, where you pass the database values to the function and it returns the result. This was the logic can be made much clearer and possibly even less complex.
# 5 Re: IIf function with fewer conditions in Access
Here is an example of Test4 redone using VBA functions: Function GetDay(tm As Date, Dy As String, Dy1 As String) As String
'Example Call From A Query
'Test4: GetDay([Tbl_Formatted_Calls]![TIME]+[Tbl_Formatted_Calls]![Duration_Time_Format],[Tbl_Dates]![Day],[Tbl_Dates]![Day+1])
'Set Default Return Value
GetDay = "Bann"
If tm > #23:59:59# Then
If MonFri(Dy) Then
If Dy1 = "Ban" Then GetDay = "Yes"
If Dy1 = "Sat" Then GetDay = "Saty"
End If
If MonFri(Dy1) Then
If Dy = "Sun" Then GetDay = "Suny"
If Dy = "Ban" Then GetDay = "Bany"
End If
End If
End Function
Function MonFri(Dy As String) As Boolean
MonFri = ((InStr("MonTueWedThuFri", Dy) + 2) Mod 3) = 0
End Function
