VBA code for selecting records by date

[upsman via AccessMonster.com]

I have the following code behind a 'Print' button.

Dim strDocName As String
Dim strWhere As String
Dim YrStart As Date

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("AttendanceCtrl")

YrStart = rst!FirstDay
strDocName = "Contact"

strWhere = "[StudentID] =" & Me!StudentId & "AND [Calls].[CallDate] >= "
& YrStart

DoCmd.OpenReport strDocName, acPreview, , strWhere

Table 'AttendanceCtrl' contains the start of the year and Table 'Call'
contains student phone calls. I want a report of only the calls made this
year for the selected student but I'm getting all the calls they've ever made.
I'm sure it has something to do with the syntax and the combination of quotes
and ampersands but I'm new to VBA and can't seem to get it right. How
should it be coded to get only this year's calls?

Thanks,
Rod

 

[Graham R Seach]

Hi Rod,

Use the Year() function.

strWhere = "[StudentID] =" & Me!StudentId & "AND Year([Calls].[CallDate]) >=
" & YrStart

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia