How to convert IIF into a Module 1

[jessedh]

Hi, I need to convert this really long IIF statement into a VB Module...Can anyone help set me on the right path.... thanks in advance

Jesse

Status: IIf([CurrentStatus] Like "DEN*" Or [CurrentStatus] Like "*with*" Or [CurrentStatus] Like "CANC*" Or [CurrentStatus] Like "*resc*","Please contact your loan officer.",IIf([CurrentStatus] Like "APPROVED","Your loan has been approved, a commitment outlining terms and conditions is being prepared.",IIf([CurrentStatus] Like "CLO*" Or [CurrentStatus] Like "SOLD" Or [CurrentStatus] Like "SHIP*" Or [CurrentStatus] Like "PORTFOLIO" Or [CurrentStatus] Like "*xf*","Your loan is closed.",IIf([CurrentStatus] Like "rt cmt*","Thank you for returning your commitment. Your loan officer will contact you shortly.",IIf([CurrentStatus] Like "OK*","The underwriting conditions are cleared. Please contact your loan officer for the next steps in scheduling your closing.",IIf([CurrentStatus] Like "ST*" Or [CurrentStatus] Like "cmT*","Your commitment has been sent.",IIf([CurrentStatus] Like "nds*" Or [CurrentStatus] Like "sub*" Or [CurrentStatus] Like "in*","Application in process","Status Not Defined&quot))))))

 

[AWithers]

You can do it as an if statement e.g.

'start of code
Private Sub ifstatement()

Dim status As String
Dim message As String

status = "portfolio"

If status Like "*folio" Then
message = "your message"
ElseIf status Like "port*" Then
message = "your second message2"

End If
End Sub
'end of code

obviously you will need to replace the variable with database/form fields

alternatively you could use a case statement

Hope this helps

Andy

 

[jimmythegeek]

Copy the following function into a module. You can now use this anywhere in your application, even in a query. If you want to know the current status string, you just pass this function the current status, and it will return the string:

In a Query:
Status: =StatusSring(CurrentStatus])

In Code on a form:
Me.txtStatus = StatusString(Me.CurrentStatus)

etc.

====================
Public Function StatusString(currStat As String) As String
If currStat Like "DEN*" Or currStat Like "*with*" Or currStat Like "CANC*" Or currStat Like "*resc*" Then
StatusString = "Please contact your loan officer."
ElseIf currStat = "APPROVED" Then
StatusString = "Your loan has been approved, a commitment outlining terms and conditions is being prepared."
ElseIf currStat Like "CLO*" Or currStat = "SOLD" Or currStat Like "SHIP*" Or currStat = "PORTFOLIO" Or currStat Like "*xf*" Then
StatusString = "Your loan is closed."
ElseIf currStat Like "rt cmt*" Then
StatusString = "Thank you for returning your commitment. Your loan officer will contact you shortly."
ElseIf currStat Like "OK*" Then
StatusString = "The underwriting conditions are cleared. Please contact your loan officer for the next steps in scheduling your closing."
ElseIf currStat Like "ST*" Or currStat Like "cmT*" Then
StatusString = "Your commitment has been sent."
ElseIf currStat Like "nds*" Or currStat Like "sub*" Or currStat Like "in*" Then
StatusString = "Application in process"
Else
StatusString = "Status Not Defined"
End If
End Function
====================

Now you can simply modify this function in the future to handle more situations by adding more "OR's", or more "ElseIf's". Ideally, you would use a Select Case Statement, but you can't use "Like" in a Case. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?