Search by year not working

[chappy68]

tblYear has two fields, YrID as autonumber and YrEnd as date type.
tblTaskDetails has a field TaskDate as a date type also.

I have a search form with a combo box cboSrchYear which is bound to tblYear. My user selects a year (I used the Year function so that only the year shows in the combo box).

I built a select query in the query builder. The criteria refers back to the selection in the search form [Forms]![frmMainMenu]![cboSrchYear]. I setup the field in the query as: TskDate: Year([TaskDate]).

With all the details above, here is my ultimate goal. I am searching for all records in tblTaskDetails with a year of XXXX. I will have many different task dates. In my sample data, I have 3 tasks with dates in 2011. When I run the query, nothing shows up.

As I read my post back before submitting, I figured out cboSrchYear on the search form is bound to the id and not the date. Is that my problem. I could make cboSrchYear bound to the YrEnd field since it will always be a unique date since I am only using the table for search purposes.

I seem to remember the Year function is just formatting and only extracts the year portion of a date. The contents of the field is still a full date.

I am just a little confused at this point.

 

[Alansidman]

In your combo box, you probably have the look up bound to the ID field, yet in your query you are trying to get the actual year. Set your criteria to the Year ID. Make this field in your query not visible.

Alan

 

[chappy68]

Just took a clear mind to figure it out. I guess writing code in the middle of the night is not the best time. My combo box was bound to the ID field. Changed it to the ClientNum field and life is good again.

Thanks Alan for the verification of what I thought it would be.

 

[Guus2005]

having a tblYear with only a year number doesn't make any sense. If you are using it to create a cartesian product somewhere in your application then it might.

Your combobox should reflect the possible choices in your database.
me.cboSrchYear.recordsource: "select distinct year([taskdate]) as TaskYear from tblTaskDetails"

Using the following query you will get all the records within the selected year:
select * from tblTaskDetails where Year([taskdate]) = me.cboTaskDetails"

You might want to change "me" with the name of your form.

HTH