RecordCount ?

[CdRsKuLL]

My head just can't suss out whats going on.. PLEASE help me before it fall's off.

I'm coding up a holiday vacation planner for work here. After lots of messing about this is how I've done my table...

tblHoliday
Fields are..
employee
holidaymonth
day01
day02
day03
day04
etc.. the day## is the day of the month.

So the table looks like..

employee - 1
holidaymonth - 11/2009
day12 - 1
day23 - 2

the 1 and 2 are the type of holiday that is is.. ie. half day etc.

Now when I'm trying to calculate how many days off holiday they have had off I'm doing this...

    Dim db As Database
    Set db = CurrentDb()
    Dim rec As Recordset
    Dim strSQL As String
    Dim n As Double
    Dim s As Double
    Dim p As Integer
    Dim recnum As Integer
    Dim intYear As Integer
    

    intYear = Year(Me.txtCalendarHeading)
 
    'count full days
    strSQL = "SELECT * FROM tblHoliday WHERE (((tblHoliday.employee)=1) AND ((tblHoliday.holidaymonth) Like '*2009'));"
    Set rec = db.OpenRecordset(strSQL)
    
    Debug.Print "number of records found is :-" & rec.RecordCount
    If rec.RecordCount > 0 Then
    For recnum = 0 To rec.RecordCount + 1
        
        For p = 1 To 31
        If rec("day" & Right("00" & p, 2)) = 1 Then n = n + 1
        If rec("day" & Right("00" & p, 2)) = 2 Then n = n + 0.5
        If rec("day" & Right("00" & p, 2)) = 3 Then n = n + 0.5
        If rec("day" & Right("00" & p, 2)) = 4 Then s = s + 1
        If rec("day" & Right("00" & p, 2)) = 5 Then s = s + 0.5
        If rec("day" & Right("00" & p, 2)) = 6 Then s = s + 0.5
        Next p
        
    rec.MoveNext
    Next recnum
    End If
    n = Int(Text130.Value) - n
    Label134.Caption = Trim(Str(n))
    rec.Close

my idea is that it loads all the table with a matching employee and holiday year ie.. 2009 then it goes through each record counting all the 1's / 2's etc..

My only problem is that for some reason its only returning 1 for recordcount ?

would it have something to do with the SELECT * ?

any help out there ?

many thanks

Steve

 

[geoffcodd]

rs.movelast
i = recordcount
rs.moverfirst

I think

 

[CdRsKuLL]

ahh.. so recordcount holds the position of the record its currently on ? not the total number of records...

sounds like a plan

going to see THANK YOU..

 

[CdRsKuLL]

Your a clever man tis working now..

thank you so much ! onward and forward now !.. lol