I am trying to write a simple application in Access with VBA. I have extensive VB6 experience, but for some reason databases have always confused me.

I tried searching but i am not sure what it is i am looking for. Essentially i want to search through tables using code, and SQL, and interact with them somewhat. Can anyone point me to a resource to help me with that?

Sorry if that is a bit confusing, what i am doing specifically is:

I need to get all the records from a table that have a date within a certain range, and then take a separate value from the first and last entries.

I then need to count hom many entries in another table have a match to that value and another arbitrary one.

Thanks

-Alex

select * from table where now() between startdate and enddate
Put this in a recordset: rst
Move around in the recordset like you normally would and get the information you want.

I then need to count how many entries in another table have a match to that value and another arbitrary one.

Use the values you found earlier and create another query and/or recordset

Put this in a recordset: rst
Move around in the recordset like you normally would and get the information you want.

This is the part i need help with, i have never used VBA with Access before, and i cant find a tutorial online that will help me

Dim rs As ADODB.Recordset
Dim FirstValue As String
Dim LastValue As String

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM YourTableName WHERE YourDate>= #" & YourStartDate & "# AND YourDate <= #" & YourEndDate & "# ORDER BY YourDate", CurrentProject.Connection, adOpenKeyset, adLockReadOnly

With rs
.MoveFirst
'Get the value you want off the first record (oldest date)
FirstValue = .Fields("YourFieldName").Value
.MoveLast
'Get the value you want off the last record (newest date)
LastValue = .Fields("YourFieldName").Value
End With

rs.Close

Set rs = Nothing

The above is simple code to open an ADO recordset, get a value from the first record, some other value from the last record, and then close the recordset. The sort (ORDER BY) in the SQL puts the oldest date as the first record and the newest date as the last record. Add DESC to the end of the SQL if you want to reverse that order (newest first).

Once you have the recordset created, use rs. and then scroll through what IntelliSense brings up to see all the different things you can do once inside the recordset.

To do counts from a different table where, for example, that other table has values that all equal FirstValue from above, just use a DCount function, like so:

CountTotal = DCount("TheFieldNameYouWantToCount","TheNameOfTheTableYouAreCountingFrom","TheFieldNameYouWantToCount=" & FirstValue)

You may have to play with single quotes and such, depending on the data types. In general, this is it:

Strings: single quote ("SELECT * FROM People WHERE Name='Fred'")
Numbers: no quotes ("SELECT * FROM People WHERE Age=4")
Dates: #date_here# ("SELECT * FROM People WHERE Birthday=#6/1/2003#")

Thanks, that mostly sorts it out.

The only problem is that now it is giving me a User-Defined Type not defined error on the ADODB.Recordset. If i take out the ADODB it gives me a "Invali use of New Keyword" error on the Set x = New Reocordset part.

any idea what i need to do?

Use DAO rather than ADO or ADODB when you are doing internal recordset stuff. So do not declare an ADO.RECORDSET, declare a DAO.RECORDSET.

FYI, here's one tutorial on vb with DAO recordsets that I found helpful starting out
http://www.devdos.com/vb/lesson4.shtml, Maybe it will be of use to you also.