Data report "report Header"

[gracehskuo]

hello,please help.how to display database field to report header .

for example:
from the database table have Department field.i want to display the Department name to report header.

this is the current code:

Dim total As Double
Dim strSQL As String
Dim conDataConnection As Connection
Dim empName As String
Dim enddate As String
Dim startdate As String
Dim Department As String
'Dim rs As New Recordset

empOTsummary.Orientation = rptOrientLandscape
empOTsummary.Show
empName = Cboempname.Text
'Department = rs1("Department")

start = Format(DTPicker1.Value, "dd MMM yyyy")
enddate = Format(DTPicker2.Value, "dd MMM yyyy")


 Set conDataConnection = New Connection
      conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
                "\Overtime.mdb;Persist Security Info=False"

Dim rs As New ADODB.Recordset
strSQL = "SELECT * FROM Overtime where OvertimeDate BETWEEN #" & start & "# and #" & enddate & "# and EmployeeName='" & empName & "'"
rs.Open strSQL, conDataConnection, adOpenStatic, adLockOptimistic

While Not rs.EOF
        If IsNull(rs("OtHours").Value) Then
            total = total + 0
        Else
            total = total + rs("OtHours").Value
        End If
        rs.MoveNext
    Wend

  'display total at report footer
  empOTsummary.Sections("Section5").Controls.Item("Label15").Caption = Format(total, "#,##0.00")
  empOTsummary.Sections("Section4").Controls.Item("Label17").Caption = rs("Department") i use this method to display department name but have error message type miss match.
  empOTsummary.Sections("Section4").Controls.Item("Label19").Caption = start
  empOTsummary.Sections("Section4").Controls.Item("Label21").Caption = enddate
'set the database source in rs
    Set empOTsummary.DataSource = rs
    'then set the field
    empOTsummary.Sections("section1").Controls.Item("Text1").DataField = "EmployeeName"

thanks for help1

[brucevde]

When that line executes, the recordset is at EOF (because of the While Wend Loop).

Add a rs.MoveFirst immediately after the Wend statement.

[gracehskuo]

brucevde:
hi,i try the method from your guide.but have error message appear


this is the current code:

While Not rs.EOF
        If IsNull(rs("OtHours").Value) Then
            total = total + 0
        Else
            total = total + rs("OtHours").Value
        End If
        rs.MoveNext
    Wend
    rs.MoveFirst

please help!

[wes4dbt]

If you are getting the error at this line: rs.movefirst, then probablely "rs" is empty. Check it with rs.recordcount.