looping through the rows in a recordset

  • Thread starter Thread starter Jonathan Stratford
  • Start date Start date
J

Jonathan Stratford

Hi,

I'm trying to create a leave system, where if the user
requests leave which includes a special day - weekends
and public holidays - then the number of days in the
leave is decreased by one. I use the following code, but
as rs.recordcount returns -1, i can't go through the
records this way. Can anyone suggest the correct way to
do this?

Many thanks,

Jonathan Stratford

* TSpecialDays is a table which contains one field -
"Special Day Date"

Set con = CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
strSQL = "SELECT [Special Day Date] FROM TSpecialDays"

rs.Open strSQL, con, , , adCmdText

For i = Return_Date To Leave_Date
If rs.EOF Then Exit For
For j = 1 To rs.RecordCount

If i = j Then leavelength = leavelength - 1
Next
Next
 
No need for a loop.

The number of special days between StartDate and EndDate is:

=DCount("*", "TSpecialDays", "[Special Day Date] Between " & _
Format([StartDate], "\#mm\/dd\/yyyy\#") & " And " & _
Format([EndDate], "\#mm\/dd\/yyyy\#"))
 
Thank you very much, that works perfectly!

Jonathan Stratford


-----Original Message-----
No need for a loop.

The number of special days between StartDate and EndDate is:

=DCount("*", "TSpecialDays", "[Special Day Date] Between " & _
Format([StartDate], "\#mm\/dd\/yyyy\#") & " And " & _
Format([EndDate], "\#mm\/dd\/yyyy\#"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Jonathan Stratford"
I'm trying to create a leave system, where if the user
requests leave which includes a special day - weekends
and public holidays - then the number of days in the
leave is decreased by one. I use the following code, but
as rs.recordcount returns -1, i can't go through the
records this way. Can anyone suggest the correct way to
do this?

Many thanks,

Jonathan Stratford

* TSpecialDays is a table which contains one field -
"Special Day Date"

Set con = CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
strSQL = "SELECT [Special Day Date] FROM TSpecialDays"

rs.Open strSQL, con, , , adCmdText

For i = Return_Date To Leave_Date
If rs.EOF Then Exit For
For j = 1 To rs.RecordCount

If i = j Then leavelength = leavelength - 1
Next
Next
Click to expand...


.
Click to expand...
 
Back
Top