#Error in Access Query and report (1 Viewer)

Erin M 2021

Member
Local time
Today, 07:54
Joined
Apr 14, 2021
Messages
33
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
18,742
Hi. Welcome to AWF!

#Error could mean a lot of things. It's hard to troubleshoot it if you're using that function in a Textbox.

It might be simpler if you can store the account numbers in a table, so you can just look them up.
 

Erin M 2021

Member
Local time
Today, 07:54
Joined
Apr 14, 2021
Messages
33
Hi. Welcome to AWF!

#Error could mean a lot of things. It's hard to troubleshoot it if you're using that function in a Textbox.

It might be simpler if you can store the account numbers in a table, so you can just look them up.
Thanks for the response! There is still a learning curve with this report. What would that look like in terms of each criteria met? If fundcode = 610 and constituency = alumni then 1? and repeat for each?
 

plog

Banishment Pending
Local time
Today, 07:54
Joined
May 11, 2011
Messages
10,864
You need to find what the input values are for the function when you receive the error. What values are you passing?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
18,742
Thanks for the response! There is still a learning curve with this report. What would that look like in terms of each criteria met? If fundcode = 610 and constituency = alumni then 1? and repeat for each?
If you had a table like so:
Code:
Acct    FundCode    Constituency
1       610         alumni
etc...
Then it would be easy to look up the value you want from it.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 08:54
Joined
Jan 23, 2006
Messages
14,240
Agree with DBGuy - sounds like a Table with fundcode,constituency and PSaccount would be simpler.
 

Erin M 2021

Member
Local time
Today, 07:54
Joined
Apr 14, 2021
Messages
33
If you had a table like so:
Code:
Acct    FundCode    Constituency
1       610         alumni
etc...
Then it would be easy to look up the value you want from it.
Makes sense. I'll give this a shot. Many thanks!
 

Erin M 2021

Member
Local time
Today, 07:54
Joined
Apr 14, 2021
Messages
33
Ok, I went ahead and just stuck the criteria in to my SQL as I was having issues with linking the table to my imported DB tables. It's working. However, I've run in to an error when having two sets of criteria left. I suspect I've done too many nested statements. Any suggestions for a work around? I need to add two more IIFs. Many thanks.

SELECT Gift.Gf_Constit_Code AS Constituency, Gift.Gf_Amount AS GiftAmount, GfFnds_1.GfFnds_1_Amount AS SplitAmount, CFSFundCode.GfFnds_1FnAtrCat_1_Description AS FundCode, IIf([fundcode]='440' And [Constituency] Like "*Alumni*" Or "*Other Individual*",'450070',0) AS PSAccount, IIF([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620" AND ([Constituency]="Alumni"),'450440',

IIF([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620" AND ([Constituency]="Parent" OR [Constituency]="Staff" OR [Constituency]="Student" OR [Constituency]="Other Individual"),'450430',

IIF([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620" AND ([Constituency]="Foundation"),'450420',

IIF([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620" AND ([Constituency]="Corporation"),'450410',

IIF([FundCode]="460" OR [FundCode]="610" OR [FundCode]="620" AND ([Constituency]="Fund-Raising Organization" OR [Constituency]="Religious Organization" OR [Constituency]="Other Organization"),'450450',

IIF([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910" AND ([Constituency]="Alumni"),'450330',

IIF([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910" AND ([Constituency]="Parent" Or [Constituency]="Staff" Or [Constituency]="Student" Or [Constituency]="Other Individual"),'450330',

IIF([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910" AND ([Constituency]="Foundation"),'450320',

IIF([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910" AND ([Constituency]="Corporation"),'450310',

IIF([FundCode]="810" OR [FundCode]="815" OR [FundCode]="820" OR [FundCode]="870" OR [FundCode]="873" OR [FundCode]="874" OR [FundCode]="875" OR [FundCode]="877" OR [FundCode]="890" OR [FundCode]="910" AND ([Constituency]="Fund-Raising Organization" OR [Constituency]="Religious Organization" OR [Constituency]="Other Organization"),'450350',

IIF([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455" AND ([Constituency]="Alumni"),'450070',

IIF([FundCode]="120" OR [FundCode]="130" OR [FundCode]="210" OR [FundCode]="220" OR [FundCode]="230" OR [FundCode]="233" OR [FundCode]="235" OR [FundCode]="240" OR [FundCode]="260" OR [FundCode]="310" OR [FundCode]="415" OR [FundCode]="418" OR [FundCode]="428" OR [FundCode]="440" OR [FundCode]="445" OR [FundCode]="448" OR [FundCode]="450" OR [FundCode]="455" AND ([Constituency]="Parent" OR [Constituency]="Staff" OR [Constituency]="Student" OR [Constituency]="Other Individual"),'450050',3)))))))))))) AS PSAccount, IIf([FundCode]="815" Or [FundCode]="820" Or [FundCode]="870" Or [FundCode]="873" Or [FundCode]="874" Or [FundCode]="875" Or [FundCode]="877" Or [FundCode]="890" And ([Constituency]="Parent" Or [Constituency]="Staff" Or [Constituency]="Student" Or [Constituency]="Other Individual"),'450330',0) AS PSAccount3

FROM (((Gift LEFT JOIN GfFnds_1 ON Gift.GfFnds_1_LINK = GfFnds_1.GfFnds_1_LINK) LEFT JOIN CFSFundType ON GfFnds_1.GfFnds_1Fn_LINK = CFSFundType.GfFnds_1Fn_LINK) LEFT JOIN GfFnds_1FnAtr ON CFSFundType.GfFnds_1FnAtr_LINK = GfFnds_1FnAtr.GfFnds_1FnAtr_LINK) LEFT JOIN CFSFundCode ON GfFnds_1FnAtr.GfFnds_1FnAtrCat_1_LINK = CFSFundCode.GfFnds_1FnAtrCat_1_LINK;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
18,742
Hi. Is there any way you can layout the "rules" for us? For example, if I had something like this:

Code:
IIf(Grade>90,"A",IIf(Grade>80 And Grade<=90,"B",IIf(Grade>70 And Grade<=80,"C","D")))

I would layout the rules like this:

GradeMinMax
A91
B8190
C7180
D70
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:54
Joined
Oct 29, 2018
Messages
18,742
Hi. Thanks. That's a good start. Is there any way you can convert that into an Excel file?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 28, 2001
Messages
22,491
I'm thinking a JOIN to a lookup table might do the trick. Might be a pain to generate the table the first time, but after that, no biggie.

Right now you've got a FUNDCODE and a CONSTITUENCY, which you then try to analyze with a complex IIF ladder to get something you call PSAccount3. But if you had a 3-field table of FUNDCODE, CONSTITUENCY, and PSAccount3 for all combinations of FUNDCODE and CONSTITUENCY, you could do a LEFT JOIN on that table (on FUNDCODE and on CONSTITUENCY) to do the lookup for you. If you get back a null you can use the NZ function to supply whatever is the default. For the example, call the lookup table FCLookup. Then your solution MIGHT resemble...

Code:
SELECT ......, NZ( FCLookup.PSAcct3, default value) , ...
FROM ....., GfFnd_1 LEFT JOIN FCLookup ON GfFnd_1.FUNDCODE = FCLookup.FUNDCODE AND GfFnd_1.CONSTITUENCY = FCLookup.CONSTITUENCY, ...

Doing it this way means if you ever have to add either fundcodes or constituents, all you do is expand your lookup table to include the new codes or groups.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
16,077
Code:
Public Function PSAccount(sFundType As Variant, lFundCode As Variant, sConstituency As Variant) As Long
    
    sFundType = sFundType & ""
    sConstituency = sConstituency & ""
    lFundCode = Val(lFundCode & "")
    
    PSAccount = 0
    
    If sFundType = "Sales & Service" Then
        PSAccount = 470030
        Exit Function
    End If
    
    Select Case lFundCode
        Case 460, 610, 620
    
            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
            End Select
    
        Case 810, 815, 820, 870, 873, 874, 875, 877, 890
    
            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
            End Select
    
        Case 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
            End Select
    
    End Select

End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:54
Joined
May 7, 2009
Messages
16,077
call this function from your Query:
Code:
Public Function PSAccount(sFundType As Variant, lFundCode As Variant, sConstituency As Variant) As Long
    
      
    sFundType = sFundType & ""
    sConstituency = sConstituency & ""
    lFundCode = Val(lFundCode & "")
    
    PSAccount = 0
    
    If sFundType = "Sales & Service" Then
        PSAccount = 470030
        Exit Function
    End If
    
    '''''''''''''''''''''''''''''''''
    'note
    '
    'you replace "yourTableNameHere" with
    'the correct name of your New PSA Table
    '
    '''''''''''''''''''''''''''''''''
    PSAccount = Nz(DLookup("PSAccountCode", "yourTableNameHere", "PSFundCode = " & lfuncode & " And PSConstituency = '" & sConstituency & "'"), 0)
    

End Function
 

Erin M 2021

Member
Local time
Today, 07:54
Joined
Apr 14, 2021
Messages
33
Ok folks, I was able to get all needed data into my query. Yay!
Now, I've created my report (that is to be exported to excel and sent to accounting). In Report View, everything looks great. But when I export it, fields are missing are fields are in the wrong places. Any ideas?
 

plog

Banishment Pending
Local time
Today, 07:54
Joined
May 11, 2011
Messages
10,864
If excel is the intended target, why make a report? Just export your query directly to it.
 

Erin M 2021

Member
Local time
Today, 07:54
Joined
Apr 14, 2021
Messages
33
If excel is the intended target, why make a report? Just export your query directly to it.
I have to create a pseudo line of data. My data only shows debit account info. So I've got two lines in detail. One with actual imported data and one that is just text fields with the credit account info.
 

Users who are viewing this thread

Top Bottom