using variables to manipulate recordset 1

[Klepdog]

I have zero experience with writing code, but what I have read in a vba book for dummies. I am trying to create a recordset then filter all records by using a user inputted year variable (myYear). I have been able to write the code neccessary to get the recordset, but that has been it. Every time I try to manipulate the recordset to get a specific year I get errors.The varible myYear is being pulled from a textbox on a form called frmExport. My goal is to find all records for let say "2005"(as a user variable". My intentions is to enventually send this data to an Excel file and delete from the database

Private Sub Command)_Click()

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim myYear as String

Set db = CurrentDb

If IsNull(Forms!frmExport!Text1.Value) Then
MsgBox "You have not entered a year to Export."
Me!Text1.SetFocus
End If

myYear = Me!Text1.Value
Set rs = db.OpenRecordset("tblFixed"' dbOpenTable)

End Sub

Thanks for any help.
Don

 

[MajP]

1) Normally this is done by opening a recordset based on a sql string
2) Dates in a sql string must be deliminited #1/1/2011#
Private Sub Command)_Click()

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim myYear as String
Dim strSql as string

Set db = CurrentDb

If not Isdate(Forms!frmExport!Text1.Value) Then
MsgBox "You have not entered a year to Export."
Me!Text1.SetFocus
else
myYear = "#" & Me!Text1.Value & "#"
strSql = "SELECT * from tblFixed where someDateField = " & myYear
Set rs = db.OpenRecordset(strSql,dbopendynaset)
end if
End Sub

 

[Klepdog]

I sure appreciate this. Could I at this point use the docmd.OutputTo the Excel file? Or would I use a different command?

Don

 

[Klepdog]

I just remembered that the Date field in the table tblFixed.Date uses the short date format. Would this affect the code that you gave?

 

[MajP]

The display format on a date field is just a format applied to the data. Behind the scenes all dates are stored as an integer representing elapsed days since a base date (31 dec 1899). Time is represented as the fraction of a day part. So today at 12:00pm is 40850.5.

The easiest way to export the data to excel is to create a query. The query can have a reference to a control on a form as the criteria. So if you have a date field you could have the following in the criteria
Forms![YourFormName]![YourControlName]

When you open the query it will return records with the date that match the date in your form. Then you can use the transferspreadsheet method on that query.