Hi guys
If I select the date from 06/01/2012 to 10/01/2012 then the control goes to EOF and displays message No Record,operation cancelled but in actual in the database there is a record with 06/01/2012 date. And if I select 01/01/2012 to 10/01/2012 then it works fine.
Can anyone please figureout my problem?
Public Function func(a As String, i As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=J:\a.mdb;"
Set rs = New ADODB.Recordset
Dim wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Set wb = ActiveWorkbook
Dim i1 As Integer
Dim j1 As Integer
Dim k1 As Integer
Dim l1 As Integer
Dim m1 As Integer
Dim n1 As Integer
Set ws = ThisWorkbook.Worksheets("SLAMI")
ws.Cells(2, "A").Value = "Date from: " & Format(DateSerial(ComboBox3, ComboBox2, ComboBox1), "dd/mm/yyyy") & " to " & Format(DateSerial(ComboBox6, ComboBox5, ComboBox4), "dd/mm/yyyy")
strsql = "SELECT * FROM tblinputvol WHERE RecDate >= #" & Format(DateSerial(ComboBox3, ComboBox2, ComboBox1), "dd/mm/yyyy") & " # AND RecDate <= # " & Format(DateSerial(ComboBox6, ComboBox5, ComboBox4), "dd/mm/yyyy") & " # and document='" & a & "'"
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
If rs.EOF = True Then
MsgBox "No Record, Operation cancelled"
Exit Function
End If
rs.MoveFirst
i1 = 0
j1 = 0
k1 = 0
l1 = 0
m1 = 0
n1 = 0
Do Until rs.EOF
w = Work_Days(rs.Fields("RecDate").Value, Format(Date, "dd/mm/yyyy"))
If w = 1 Then
i1 = i1 + rs.Fields("outstanding").Value
ElseIf w = 2 Then
j1 = j1 + rs.Fields("outstanding").Value
ElseIf w = 3 Then
k1 = k1 + rs.Fields("outstanding").Value
ElseIf w = 4 Then
l1 = l1 + rs.Fields("outstanding").Value
ElseIf w = 5 Then
m1 = m1 + rs.Fields("outstanding").Value
ElseIf w > 5 Then
n1 = n1 + rs.Fields("outstanding").Value
End If
rs.MoveNext
Loop
rs.Close
ws.Cells(i, 2).Value = i1
ws.Cells(i, 3).Value = j1
ws.Cells(i, 4).Value = k1
ws.Cells(i, 5).Value = l1
ws.Cells(i, 6).Value = m1
ws.Cells(i, 7).Value = n1
End Function
Bookmarks