ADO Find Method 
Author Message
 ADO Find Method

Tools: VB 6.0, SQL Server 6.5

Is it possible to use the Find method in a Loop?
My code bombs durring the second loop at the point of the Find method?

Code snipit.....

'Calculate Number of days between dates passed in
lngNumDays = DateDiff("d", dtePeriodBegin, dtePeriodEnd)

            For lngCounter = 0 To lngNumDays

                intDayOfWeek = DatePart("w", dteNextDate)
                Select Case intDayOWeek
                    'If the date is a workday
                    Case 2 To 6
                        .Find "HOLIDAYDATE = #" & dteNextDate & "#"
                        If (.Fields!HOLIDAYDATE = dteNextDate) Then
                            sngHours = sngHours + (.Fields!FULLDAY * 8)
                        Else:
                            sngHours = sngHours + 8
                        End If
                    End Select
                dteNextDate = DateAdd("d", 1, dteNextDate)

            Next lngCounter

'Holidaydate and FULLDAY comes from a table that stores Holidays and
'there equivilant "days off" value
'I.E.  HolidayDate = 07/04/1999 FULLDAY = 1

I tried both a .MoveFirst and seting a bookmark to the first position of
the recordset, then setting the position of the pointer in the recordset
to the first record via the bookmark.  Neither works.

I would appreciate the help!



Mon, 07 Jan 2002 03:00:00 GMT
 ADO Find Method

Sir,

Thanks for the advice...

However, note that I state the code bombs on the SECOND time though.

The syntax for the dates is correct. I coppied the syntax verbatim from
MSDN.

In addition, I tested the code with a start date of '01/01/1998' which is
in fact a holiday.  The code executes correctly for the Jan 1 holiday.

Again,  the problem rest in the fact that on the second time through the
code errors on the If statement.

I believe the problem rest in the fact that I do not fully understand what
happens to absolute position in relationship to the Find method.

Thanks again.

Quote:

> Your code bombs because SQL Server doesn't wrap #'s around dates.  Your
> subject says ADO so I'm assuming that's what you're using.  use single
> quotes instead.  # is for Access/DAO.  I did not look at the rest of the
> setup so there could be more.

> regards,
> vG



> > Tools: VB 6.0, SQL Server 6.5

> > Is it possible to use the Find method in a Loop?
> > My code bombs durring the second loop at the point of the Find method?

> > Code snipit.....

> > 'Calculate Number of days between dates passed in
> > lngNumDays = DateDiff("d", dtePeriodBegin, dtePeriodEnd)

> >             For lngCounter = 0 To lngNumDays

> >                 intDayOfWeek = DatePart("w", dteNextDate)
> >                 Select Case intDayOWeek
> >                     'If the date is a workday
> >                     Case 2 To 6
> >                         .Find "HOLIDAYDATE = #" & dteNextDate & "#"
> >                         If (.Fields!HOLIDAYDATE = dteNextDate) Then
> >                             sngHours = sngHours + (.Fields!FULLDAY * 8)
> >                         Else:
> >                             sngHours = sngHours + 8
> >                         End If
> >                     End Select
> >                 dteNextDate = DateAdd("d", 1, dteNextDate)

> >             Next lngCounter

> > 'Holidaydate and FULLDAY comes from a table that stores Holidays and
> > 'there equivilant "days off" value
> > 'I.E.  HolidayDate = 07/04/1999 FULLDAY = 1

> > I tried both a .MoveFirst and seting a bookmark to the first position of
> > the recordset, then setting the position of the pointer in the recordset
> > to the first record via the bookmark.  Neither works.

> > I would appreciate the help!



Mon, 07 Jan 2002 03:00:00 GMT
 ADO Find Method
Your code bombs because SQL Server doesn't wrap #'s around dates.  Your
subject says ADO so I'm assuming that's what you're using.  use single
quotes instead.  # is for Access/DAO.  I did not look at the rest of the
setup so there could be more.

regards,
vG


Quote:
> Tools: VB 6.0, SQL Server 6.5

> Is it possible to use the Find method in a Loop?
> My code bombs durring the second loop at the point of the Find method?

> Code snipit.....

> 'Calculate Number of days between dates passed in
> lngNumDays = DateDiff("d", dtePeriodBegin, dtePeriodEnd)

>             For lngCounter = 0 To lngNumDays

>                 intDayOfWeek = DatePart("w", dteNextDate)
>                 Select Case intDayOWeek
>                     'If the date is a workday
>                     Case 2 To 6
>                         .Find "HOLIDAYDATE = #" & dteNextDate & "#"
>                         If (.Fields!HOLIDAYDATE = dteNextDate) Then
>                             sngHours = sngHours + (.Fields!FULLDAY * 8)
>                         Else:
>                             sngHours = sngHours + 8
>                         End If
>                     End Select
>                 dteNextDate = DateAdd("d", 1, dteNextDate)

>             Next lngCounter

> 'Holidaydate and FULLDAY comes from a table that stores Holidays and
> 'there equivilant "days off" value
> 'I.E.  HolidayDate = 07/04/1999 FULLDAY = 1

> I tried both a .MoveFirst and seting a bookmark to the first position of
> the recordset, then setting the position of the pointer in the recordset
> to the first record via the bookmark.  Neither works.

> I would appreciate the help!



Tue, 08 Jan 2002 03:00:00 GMT
 ADO Find Method

Quote:

> Is it possible to use the Find method in a Loop?
> My code bombs durring the second loop at the point of the Find method?

> Code snipit.....

> I tried both a .MoveFirst and seting a bookmark to the first position of
> the recordset, then setting the position of the pointer in the recordset
> to the first record via the bookmark.  Neither works.

> I would appreciate the help!

Hi.

The problem is most likely the fact that when Find doesn't match, cursor
moves in last record. So in your loop second search result is EOF and thus
an error.

Jukka



Tue, 08 Jan 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. ADO find method compare with DAO findfirst method...

2. ADO Find method!

3. ADO Find Method

4. ADO: Find Method

5. ADO Find Method

6. I Need Help ADO Find Method

7. ADO Find method: only one criteria?

8. ADO Find Method Question

9. ADO find method

10. ADO find method

11. Problem with ADO find method (too many records)

12. ADO Find Method ??


 
Powered by phpBB® Forum Software