Re: How to use ADO Seek Method With Multiple Columns

Orlanzo wrote on Tue, 13 Jun 2006 06:46:01 -0700:

hi All,

I've got a small table in a jet database with three fields (rpt_pd,
beging_dt, and end_dt) All fields are indexed separately and I have a
compound index named "Reporting_Period" which is comprised of both
begin_dt and end_dt. The table contains 13 rows.

I'd like to use the Seek method to locate the row where a date falls
between begin_dt and ed_dt. It isn't clear to me how this would be
accomplished. Initially, I consisdered using the Find method; however, it
doesn't support a complex search criteria.

How could I rewrite the following to use the Seek method? Or, is their
another solution I should consider?

adoRst.Find("Begin_dt <= #07/25/2005# AND End_dt >= #07/25/2005#")

Kind regards,
Orlanzo

Neither Find or Seek will help you here. Can you not using a query with a
WHERE clause to narrow your results?

The other solution would be to loop through the recordset and compare the
column values, but this is very inefficient. However, if you can't add a
WHERE clause to your recordset source, you might not have much choice, eg.

adoRst.MoveFirst
Do Until adoRst.EOF
If adoRst("Begin_dt") <= CDate("07/25/2005") And adoRst("End_dt") >=
CDate("07/25/2005") Then
'do something with the row, eg. add to a disconnected recordset if you
need to do further manipulation
End If
adoRst.MoveNext
Loop


Dan


.

Relevant Pages

  • Re: WHERE syntax v. JOIN ON syntax
    ... with the non-specific join using the where clause, ... Jet supports them but not with the QBE. ... This join will produce an updatable recordset unless something other than ... Do they parse the SELECT statement that (I ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with a DAO to ADO conversion
    ... and loop through the Fields collection of the ... resultant recordset, looking at the names of each field. ... Doug Steele, Microsoft Access MVP ... If the BE's a Jet database, then it's not ODBC: ...
    (microsoft.public.access.formscoding)
  • Re: DoCmd.OpenForm
    ... Me refers to the form or report on which the code is running. ... If you do intend to have a WHERE clause, the clause should refer to a field ... Notice that this will limit the recordset of the form being opened to those ... > getting a Debug Error saying it can't find the name of my form in the ...
    (microsoft.public.access.formscoding)
  • RE: Dynamic WHERE return from a function
    ... Then you can join from the recordset. ... > string containing the the WHERE portion for all versions of that date. ... > This needs to be then inserted into a WHERE clause. ... > attribute driven table so the field in SQL holding the value can be ANYTHING ...
    (microsoft.public.sqlserver.programming)
  • Re: I think Im having connection issues
    ... the SQL AS clause creates an alias. ... ADO fails because there is no underlying field to update. ... > I'm trying to connect to an ADO recordset coming from a Pervasive ... > Set cnn = New ADODB.Connection ...
    (microsoft.public.vb.database.ado)