VBA code for selecting records by date

  • Thread starter Thread starter upsman via AccessMonster.com
  • Start date Start date
U

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
 
Hi Rod,

Use the Year() function.

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

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top