code help

[ptw78]

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".

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")))))

 

[MajP]

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.

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])

 

[ptw78]

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???

 

[MajP]

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.

 

[ptw78]

ok, now on some of the vba, what is Variant?
then i see Nz, what is Nz for?

 

[MajP]

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

 

[ptw78]

ok, i think i got it., thx

 

[ptw78]

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.

 

[MajP]

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

 

[ptw78]

yes that is correct 2 is always FHA, 3, 4 alwayas VA

 

[MajP]

Then maybe
if 2 then vha
else if 3 or 4 then "VA"
else check cd

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

 

[dhookom]

I agree with MajP's first suggestion of

You could do this much easier with a reference table and then simply link to it.

Data belongs in tables, not in code.

Duane
Hook'D on Access
MS Access MVP