Filtering Records

[Zvonko]

Hello!

I need some help with SQL statements. My question is:
How can I retrieve from my DB those records that were last accessed between two dates, let's say that I have to retrive all records from 2. june 2000 to 1. october 2000.
Does anyone know how?

[BruceG]

I can give you an example using Jet/DAO. If you are using SQL Server/ADO or some other setup the code would be different.

Assuming there is a Date/Time field in your records, you could do something like the following:

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = OpenDatabase("C:\Whatever\MyData.mdb")
strSQL = "SELECT * FROM MyTable " _
       & "WHERE DateField BETWEEN #6/2/2000# AND #10/1/2000#"           
Set rst = dbs.OpenRecordset(strSQL)
' "rst" is now a recordset containing the filtered records
' you want to process ...

Do Until rst.EOF
    'whatever
    rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set dbs = Nothing

[Zvonko]

And if I don't have date fields?
Let's say that I have text fields and I want to retrieve just those records that start with charachters between A and K? Does previous method work here too?

[BruceG]

Yes. You could use:

strSQL = "SELECT * FROM MyTable " _
       & "WHERE Left(SomeField, 1) BETWEEN 'A' AND 'K'"

Note: For Jet (Access) databases, you can use VB functions (such as "Left") in the SQL. The SQL for other DBMS's have their own specific functions that achieve the same results as the corresponding VB function.

[Zvonko]

I use DataEnvironment/ADO. Will that work also with this or not?