asked on 1/27/2016

Openform to display filtered records by year

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?

Microsoft Access

Last Comment

Dale Fye

1/27/2016

Rey Obrero (Capricorn1)

1/27/2016

try,

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

or

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

SteveL13

1/27/2016

ASKER

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

1/27/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Dale Fye

1/27/2016

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