IIf statement in the Report

[winxmun]

Hi There,

I've a IIf statement in a report that is too long, anyone can help to re-construct the statement? I've tried few ways but not successful...



=IIf([Refund_Legal]=-1 And [Refund_GV]=-1 And [Refund_CB]=-1,"Please note that there is legal subsidy of " & Format([Legal_Fee],"Currency") & ", gift voucher of " & Format([Gift_Voucher_Fee],"Currency") & " and cashback of " & Format([CashBack_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",IIf([Refund_Legal]=-1 And [Refund_GV]=-1,"Please note that there is legal subsidy of " & Format([Legal_Fee],"Currency") & " and gift voucher of " & Format([Gift_Voucher_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",IIf([Refund_Legal]=-1 And [Refund_CB]=-1,"Please note that there is legal subsidy of " & Format([Legal_Fee],"Currency") & " and cashback of " & Format([CashBack_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",IIf([Refund_GV]=-1 And [Refund_CB]=-1,"Please note that there is gift voucher of " & Format([Gift_Voucher_Fee],"Currency") & " and cashback of " & Format([CashBack_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",IIf([Refund_Legal]=-1,"Please note that there is legal subsidy of " & Format([Legal_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",IIf([Refund_GV]=-1,"Please note that there is gift voucher of " & Format([Gift_Voucher_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",IIf([Refund_CB]=-1,"Please note that there is cashback of " & Format([CashBack_Fee],"Currency") & " which shall be borne by the Customer(s). In this connection, kindly collect the said sum on our behalf. You are advised to execute the document only upon receipt of the said sum.",""))))))) & "
" & "
"

[hansup]

Substitute a function, maybe "LegalSpeak()", for your nested IIf expression. I suspect it will be easier to follow the logic when it is re-written in "If ... Then End If" style in a function rather than the nested IIf approach.

[winxmun]

hi hansup,
I am lost, can elaborate more....

[hansup]

Nuts. I didn't want to have to parse those IIf statements, but once I did, I saw a better way than I originally considered.

Try this as:
= LegalSpeak(Legal_Fee, Gift_Voucher_Fee, CashBack_Fee)
in place of what you had before.

I eliminated Refund_Legal, Refund_GV, and Refund_CB because I am using non-zero amount for the fees instead. If that approach doesn't work for you, you'll have to revise this thing. I liked it because it was simpler for me to deal with.

[VBA]Public Function LegalSpeak(pLegal_Fee, _
pGift_Voucher_Fee, _
pCashBack_Fee) As String

Dim strOut As String 'return value
Dim strBeginning As String
Dim strMiddle As String
Dim strEnd As String
Dim intNumFees As Integer 'number of fees
Dim colFees As Collection 'collection of formatted
'text fragments for fees

Set colFees = New Collection

strBeginning = "Please note that there is "
strEnd = " which shall be borne by the Customer(s). " & _
"In this connection, kindly collect the said sum on " & _
"our behalf. You are advised to execute the document " & _
"only upon receipt of the said sum."

intNumFees = 0

If Nz(pLegal_Fee) > 0 Then
intNumFees = intNumFees + 1
colFees.Add "legal subsidy of " & _
Format(pLegal_Fee, "Currency"), CStr(intNumFees)
End If

If Nz(pGift_Voucher_Fee) > 0 Then
intNumFees = intNumFees + 1
colFees.Add "gift voucher of " & _
Format(pGift_Voucher_Fee, "Currency"), CStr(intNumFees)
End If

If Nz(pCashBack_Fee) > 0 Then
intNumFees = intNumFees + 1
colFees.Add "cashback of " & _
Format(pCashBack_Fee, "Currency"), CStr(intNumFees)
End If

strMiddle = vbNullString
Select Case colFees.Count
Case 1
strMiddle = colFees("1")
Case 2
strMiddle = colFees("1") & " and " & colFees("2")
Case 3
strMiddle = colFees("1") & ", " & colFees("2") & _
" and " & colFees("3")
Case Else
'leave strMiddle as null string
End Select

If Len(strMiddle) > 0 Then
strOut = strBeginning & strMiddle & strEnd
Else
strOut = "No Fees"
End If

Set colFees = Nothing
LegalSpeak = strOut
End Function
[/VBA]

[winxmun]

oh yes...thank you so much...it works perfectly good...

one more question, how to have a line spacing automatically after "end string" if LegalSpeak is not null?

[hansup]

I don't understand. It looks to me like LegalSpeak() is always not null. Why do you ask when not null?

[winxmun]

Sometimes LegalSpeak() will be null if no fees to be collect. Hence, I have amend the strOut = "" instead of "No Fees".

[hansup]

Sometimes LegalSpeak() will be null if no fees to be collect. Hence, I have amend the strOut = "" instead of "No Fees".

OK. Within the function, tack a carriage return and linefeed to the end of strOut when there are fees to collect:

strOut = strBeginning & strMiddle & strEnd & vbCrLf

Does that do what you need?

[winxmun]

I dont realy understand the phrase "tack a carriage...when there are fees to collect".

I tried inserting the "strOut = strBeginning & strMiddle & strEnd & vbCrLf" within the function but not successful.

Pls elaborate further. tks!

[winxmun]

Hi,

I was thing of tying to use the same way of VBA code to construct the following If statement in the Report. However I was stuck due to the various variable in between the statement. Anybody can help? Thankssssssssooooooomuch.....


="We wish to inform you that the final instalment for the above mentioned has been paid on " & Format([Date ],"dd mmmm yyyy") & ". In accordance with the terms of the Offer, dated " & Format([Date_of Offer],"dd mmmm yyyy") & ", a fee of " & Format([Total_Fees],"Currency") & " (comprising of " & IIf([Refund_Legal]=-1 And [Refund_GV]=-1 And [Refund_CB]=-1,"legal subsidy of " & Format([Legal_Fee],"Currency") & ", gift voucher of " & Format([Gift_Voucher_Fee],"Currency") & " and cashback of " & Format([CashBack_Fee],"Currency"),IIf([Refund_Legal]=-1 And [Refund_GV]=-1,"legal subsidy of " & Format([Legal_Fee],"Currency") & " and gift voucher of " & Format([Gift_Voucher_Fee],"Currency"),IIf([Refund_Legal]=-1 And [Refund_CB]=-1,"legal subsidy of " & Format([Legal_Fee],"Currency") & " and cashback of " & Format([CashBack_Fee],"Currency"),IIf([Refund_GV]=-1 And [Refund_CB]=-1,"gift voucher of " & Format([Gift_Voucher_Fee],"Currency") & " and cashback of " & Format([CashBack_Fee],"Currency"),IIf([Refund_Legal]=-1,"legal subsidy of " & Format([Legal_Fee],"Currency"),IIf([Refund_GV]=-1,"gift voucher of " & Format([Gift_Voucher_Fee],"Currency"),IIf([Refund_CB]=-1,"cashback of " & Format([CashBack_Fee],"Currency"),""))))))) & ") is therefore payable. " & "
" & "
"

[hansup]

I think you might have a better chance of attracting help if you simplify your example. From my point of view, it's too challenging to attempt to make sense of that large blob of text.

Strip it down to show us only the parts which are giving you trouble. Include any error message you receive when your simplified code fails.

[winxmun]

oops sorry, i was too eager to get answer.
Refer to VBA below, how do I add text field into the strBegining string? tks.


[VBA]
Public Function Fees(pLegal_Fee, pGift_Voucher_Fee, pCashBack_Fee, pActual_Redemption_Date) As String

Dim strOut As String 'return value
Dim strBeginning As String
Dim strMiddle As String
Dim strEnd As String
Dim intNumFees As Integer 'number of fees
Dim colFees As Collection 'collection of formatted
'text fragments for fees

Set colFees = New Collection

strBeginning = "We wish to inform you that the final Housing Loan instalment for the " & _
"above mortgaged property has been paid on " & Me!Actual_Redemption_Date
strEnd = " testing " & _
"collect the said sum on our behalf. You are advised to execute the Total " & _
"Discharge of Mortgage only upon receipt of the said sum. " & _
" "
[/VBA]

[hansup]

If that code is running from a form which has a control named Actual_Redemption_Date, then Me!Actual_Redemption_Date should returns the control's value to your expression.

In that case, I don't see what purpose the pActual_Redemption_Date parameter serves.

In one of your previous versions of the question, you indicated you intended to run the function as part of a query. If that is still your intention either:

1. pass Actual_Redemption_Date to the function as the pActual_Redemption_Date parameter.

Or:

2. if you want the function to reference the value of a control on a form, change Me!Actual_Redemption_Date to Forms!YourFormName!Actual_Redemption_Date