Run-time Error 2465

[foxhound]

Hello,

I have the following code in the on format property for a report. I am looking up values in a table when I open the report based upon an employee number and a union group. The first part of the "If" and the "else" code works. However, the second part does not.

1st Part:
txt_sick_column1heading = "Bank 100%"
txt_sick_column1row1 = DLookup([StartBankSick100Hours], "[Employee Snapshot]", "[EmpNo]=Reports![Individual Detail Reports]![EmpNo]")

2nd Part:
txt_sick_column1row2 = DLookup([UsedBankSick100Hours], "[Employee Snapshot]", "[EmpNo]=Reports![Individual Detail Reports]![EmpNo]")
txt_sick_column1row3 = DLookup([AvailBankSick100Hours], "[Employee Snapshot]", "[EmpNo]=Reports![Individual Detail Reports]![EmpNo]")

I keep getting a run-time error '2465' stating that the field [UsedBankSick100Hours] can't be found. However, I know that it exists in the same table and is setup exactly like [StartBankSick100Hours] which is working fine.

Does anyone have any suggestions?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If [Group] = "Union1" Then
        txt_sick_column1heading = "Bank 100%"
        txt_sick_column1row1 = DLookup([StartBankSick100Hours], "[Employee Snapshot]", "[EmpNo]=Reports![Individual Detail Reports]![EmpNo]")
        txt_sick_column1row2 = DLookup([UsedBankSick100Hours], "[Employee Snapshot]", "[EmpNo]=Reports![Individual Detail Reports]![EmpNo]")
        txt_sick_column1row3 = DLookup([AvailBankSick100Hours], "[Employee Snapshot]", "[EmpNo]=Reports![Individual Detail Reports]![EmpNo]")
    Else
        txt_sick_column1heading = ""
        txt_sick_column1row1 = ""
        txt_sick_column1row2 = ""
        txt_sick_column1row3 = ""
    End If
    

End Sub

 

[Norie]

Check that the problem field is actually named [UsedBankSick100Hours] in the report.

 

[foxhound]

Hi Norie,

I am actually using a text box named txt_sick_column1row2 in the report. I am doing this because I want this text box to be dynamic. Meaning that the value of each text box is variant upon the [group] that each employee belongs to. For some employees I want [UsedBankSick100Hours] to appear in txt_sick_column1row2 field while for others I want that value to appear in txt_sick_column3row2 field.