Erin M 2021
Member
- Local time
- Yesterday, 18:11
- Joined
- Apr 14, 2021
- Messages
- 77
I've inherited a report and am receiving #Error in this specific field that is using the following VBA code. 'Give me the account number when the following criteria is met.'
Public Function PSAccount(sFundType As String, lFundCode As Long, sConstituency As String) As Long
If Not IsNull(sFundType) And sFundType = "Sales & Service" Then
PSAccount = 470030
ElseIf lFundCode = 460 Or lFundCode = 610 Or lFundCode = 620 Then
Select Case sConstituency
Case "Alumni": PSAccount = 1
Case "Parent": PSAccount = 2
Case "Staff": PSAccount = 3
Case "Student": PSAccount = 4
Case "Other Individual": PSAccount = 5
Case "Foundation": PSAccount = 6
Case "UT Foundation": PSAccount =7
Case "Corporation": PSAccount = 8
Case "Fund-Raising Organization": PSAccount = 9
Case "Religious Organization": PSAccount = 10
Case "Other Organization": PSAccount = 11
Case Else: PSAccount = 0
End Select
ElseIf lFundCode = 810 Or lFundCode = 815 Or lFundCode = 820 Or lFundCode = 870 Or lFundCode = 873 Or lFundCode = 874 Or lFundCode = 875 Or lFundCode = 877 Or lFundCode = 890 Then
Select Case sConstituency
Case "Alumni": PSAccount = 12
Case "Parent": PSAccount = 13
Case "Staff": PSAccount = 14
Case "Student": PSAccount = 15
Case "Other Individual": PSAccount = 16
Case "Foundation": PSAccount =17
Case "UT Foundation": PSAccount = 18
Case "Corporation": PSAccount = 19
Case "Fund-Raising Organization": PSAccount = 20
Case "Religious Organization": PSAccount = 21
Case "Other Organization": PSAccount = 22
Case Else: PSAccount = 0
End Select
Else
Select Case sConstituency
Case "Alumni": PSAccount = 23
Case "Parent": PSAccount = 24
Case "Staff": PSAccount = 25
Case "Student": PSAccount = 26
Case "Other Individual": PSAccount = 27
Case "Foundation": PSAccount = 28
Case "UT Foundation": PSAccount = 29
Case "Corporation": PSAccount = 30
Case "Fund-Raising Organization": PSAccount = 31
Case "Religious Organization": PSAccount = 32
Case "Other Organization": PSAccount = 33
Case Else: PSAccount = 0
End Select
End If
End Function
Public Function PSAccount(sFundType As String, lFundCode As Long, sConstituency As String) As Long
If Not IsNull(sFundType) And sFundType = "Sales & Service" Then
PSAccount = 470030
ElseIf lFundCode = 460 Or lFundCode = 610 Or lFundCode = 620 Then
Select Case sConstituency
Case "Alumni": PSAccount = 1
Case "Parent": PSAccount = 2
Case "Staff": PSAccount = 3
Case "Student": PSAccount = 4
Case "Other Individual": PSAccount = 5
Case "Foundation": PSAccount = 6
Case "UT Foundation": PSAccount =7
Case "Corporation": PSAccount = 8
Case "Fund-Raising Organization": PSAccount = 9
Case "Religious Organization": PSAccount = 10
Case "Other Organization": PSAccount = 11
Case Else: PSAccount = 0
End Select
ElseIf lFundCode = 810 Or lFundCode = 815 Or lFundCode = 820 Or lFundCode = 870 Or lFundCode = 873 Or lFundCode = 874 Or lFundCode = 875 Or lFundCode = 877 Or lFundCode = 890 Then
Select Case sConstituency
Case "Alumni": PSAccount = 12
Case "Parent": PSAccount = 13
Case "Staff": PSAccount = 14
Case "Student": PSAccount = 15
Case "Other Individual": PSAccount = 16
Case "Foundation": PSAccount =17
Case "UT Foundation": PSAccount = 18
Case "Corporation": PSAccount = 19
Case "Fund-Raising Organization": PSAccount = 20
Case "Religious Organization": PSAccount = 21
Case "Other Organization": PSAccount = 22
Case Else: PSAccount = 0
End Select
Else
Select Case sConstituency
Case "Alumni": PSAccount = 23
Case "Parent": PSAccount = 24
Case "Staff": PSAccount = 25
Case "Student": PSAccount = 26
Case "Other Individual": PSAccount = 27
Case "Foundation": PSAccount = 28
Case "UT Foundation": PSAccount = 29
Case "Corporation": PSAccount = 30
Case "Fund-Raising Organization": PSAccount = 31
Case "Religious Organization": PSAccount = 32
Case "Other Organization": PSAccount = 33
Case Else: PSAccount = 0
End Select
End If
End Function