Open a report based on the year

[jjake]

I have a tbl with the following fields

[tblTask]

TaskID,TaskDate, Task

I have a search form a combo box filled with years, cboYear

2016
2017
2018 etc..

I have a report that displays tasks with a date field on it which takes data from tblTask. [TaskDate]

There will only be one Task completed within the Year.

The goal...

I would like to be able to select my year from the combo box and open the report to the TaskID that has a date within the specific year.

e.g

[tblTask]

TaskID,TaskDate, Task
1,2/16/2016, Test
2,3/24/2017, Test
3,5/8/2018, Test

If i select year 2017 it will filter my report to show taskID (2)

 

[Ranman256]

the query for the report looks at the combo box on the form...
query: select * from table where [year]=forms!myForm!cboYr

 

[jjake]

Ok let me take a step back and try another situation.

I would like to open a form this time.

If I select the year 2013, I would like to open my form [frmResults] where all records of [ResultDate] are between 1/1/2013 and 12/31/2013.

if 2014 then between 1/1/2014 and 12/31/2014 etc

 

[static]

You practically wrote it yourself right there

where ResultDate between date_from and date_to

where = "ResultDate between #dt1# and #dt2#"
where = Replace(where, "dt1", "1/1/" & Forms!myform!cboYr)
where = Replace(where, "dt2", CDate("1/1/" & Forms!myform!cboYr + 1) - 1)

DoCmd.OpenForm "frmResults", acNormal, , where

 

[jjake]

You practically wrote it yourself right there

where ResultDate between date_from and date_to

where = "ResultDate between #dt1# and #dt2#"
where = Replace(where, "dt1", "1/1/" & Forms!myform!cboYr)
where = Replace(where, "dt2", CDate("1/1/" & Forms!myform!cboYr + 1) - 1)

DoCmd.OpenForm "frmResults", acNormal, , where

Works great, thanks.