Openform to display filtered records by year

SteveL13
SteveL13 used Ask the Experts™
on
On form #1 I have a combobox that allows the user to select records from a year.  The actual date fields in the table are short date like 1/12/2016.  But the combo-box displays "2016" because of the format in the query like:

Year: Format([TargetResolutionDate],"yyyy")

But then using a command button under the combo-box I'm trying to open a 2nd form to display all the records that had a TargetResolutionDate in 2016.

I've tried this but it doesn't work:

DoCmd.OpenForm "frmProjectIssue", , , "[TargetResolutionDate] = #" & Format([cboProjectDate], "yyyy") & "#"

Can someone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try,

DoCmd.OpenForm "frmProjectIssue", , , "[TargetResolutionDate] = '" & Format([cboProjectDate], "yyyy") & "'"

or

DoCmd.OpenForm "frmProjectIssue", , , "[TargetResolutionDate] = " & Year([cboProjectDate])

Author

Commented:
Neither one worked.  Remember even though I'm formatting the combobox to only display the year part of the records there may be several records that have a date in 2016.

If it helps here is the SQL for the combo-box:

SELECT DISTINCT Format([TargetResolutionDate],"yyyy") AS [Year]
FROM tblProjectIssues;

Open in new window

Top Expert 2016
Commented:
how is data displayed in [TargetResolutionDate]?
is it like this 1/1/2016?

DoCmd.OpenForm "frmProjectIssue", , , "Year([TargetResolutionDate]) = " & me.cboProjectDate
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Dim strCriteria as string
strCriteria = "[TargetResolutionDate] >= #" & DateSerial(me.cboProjectDate, 1, 1) & "# AND " _
            & "[TargetResolutionDate] < #" & DateSerial(val(me.cboProjectDate) + 1, 1, 1) & "#"
debug.print strCriteria
DoCmd.OpenForm "frmProjectIssue", , , strCriteria

Open in new window

This will basically create a criteria string that looks like:
[TargetResolutionDate] >= #1/1/2016# AND [TargetResolutionDate] < #1/1/2017#

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial