Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

code help

Status
Not open for further replies.

ptw78

Technical User
Mar 5, 2009
155
US
I am putting this code into an access query and the last two iif statements give me a #Error result. So it's not recognizing anything for "VA", "FHA", "RISK".

Code:
Investor2: IIf([MIS.CD] In ('B','L','M','N','S','W'),"PRIVATE",IIf([MIS.CD] 
In ('C','V'),"FHLMC",IIf([MIS.CD] In ('F','Y'),"FNMA",IIf([MIS.CD] 
In ('H','P','R') And [LOAN.TYPE] In ('3','4'),"VA",IIf([MIS.CD] 
In ('H','P','R') And [LOAN.TYPE] In ('2'),"FHA", "RISK")))))
 
You could do this much easier with a reference table and then simply link to it.

tblRefCode
cdletter
loanType
refCode

B Private
L Private
...

In code i would roll my own. This works.

Code:
Public Function getcode(CD As Variant, loanType As Variant) As String
  CD = Nz(CD, "")
  loanType = Nz(loanType, 0)
  Select Case CD
    Case "B", "L", "M", "N", "S", "W"
      getcode = "Private"
    Case "C", "V"
      getcode = "FHLMC"
    Case "F", "Y"
      getcode = "FNMA"
    Case "H", "P", "R"
      If loanType = 3 Or loanType = 4 Then
        getcode = "VA"
      ElseIf loanType = 2 Then
        getcode = "FHA"
      Else
        getcode = "Risk"
      End If
    Case Else
      getcode = "Risk"
    End Select
 End Function

Investor2: getCode([mis].[CD],[loan].[type])
 
k, i'm not to familiar w/what a reference table is but i looks like you just did a sub in vba and then in the access query you referenced that sub???
 
yes about the function. Some people say they do not want to use functions but iif is a vba function not pure sql.

As for a reference table.
For ease assume you did not have loan type. Then the reference table looks like

CD code
B Private
L Private
N Private
..
W Private
C FHLMC
V FHLMC

Then you could just do an inner join.
 
ok, now on some of the vba, what is Variant?
then i see Nz, what is Nz for?
 
a variant datatype can handle null, strings, and numerics. When calling a function from a query I always use the generic variant because it is very likely you will eventually pass a Null value (empty field). This would make the function crash.

NZ is Null to zero function. It changes a null to whatever you want. So if you pass a null value to a function and you want to do some kind of string comparison you can change the null to "" (empty string). This will keep your code from crashing

dim str as string
str = nz(someFieldvalue,"")
'that does not fail
'this could fail if you pass in a null
str = someFieldvalue
 
ok, i think i got it., thx
 
k, i have a small issue, it seems that some of the fields that are "Private" should be FHA or VA, so they have a CD of "B", "L", "M", "N", "S", "W" and a loanType of 2, 3, but I need it to show FHA and it's showing Private.
 
does loan type overshadow the CD? In other words if it has a loan type of 2 is it always FHA, and a loantype of 3,4 always VA regardless of the CD?

Maybe this

Case "B", "L", "M", "N", "S", "W"
if loanType = 2 then
getCode = "FHA"
elseif loanType = 3 or loantype = 4 then
getcode = "VA"
else
getCode = "Private"
end if
 
yes that is correct 2 is always FHA, 3, 4 alwayas VA
 
Then maybe
if 2 then vha
else if 3 or 4 then "VA"
else check cd


Code:
Public Function getcode(CD As Variant, loanType As Variant) As String
  CD = Nz(CD, "")
  loanType = Nz(loanType, 0)
  if loanType = 2 then
    getCode = "FHA"
  elseif loantType = 3 or loanType = 4 then
    getCode = "VA"
  else
    Select Case CD
      Case "B", "L", "M", "N", "S", "W"
        getcode = "Private"
     Case "C", "V"
       getcode = "FHLMC"
     Case "F", "Y"
       getcode = "FNMA"
    Case "H", "P", "R"
       getcode = "Risk"
    case else
       'not sure if there are other cases 
   End Select
  end if 
End Function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top