year search

[kingsgambit]

I have records of persons holidays they have taken and sick days they have taken. The records are enter like
Start date 12/2/02
End date 14/2/02

How can a get a query to show results by entering just the year.
I wanted to put a combo box on a form so the user would choose from the list and the query would then run

 

[Jon K]

First, put a combo box on a form. In the Properties sheet, change the combo box Name to cboLeaveYear. Change its Row Source Type to Value List. Type the 4-digit years in the Row Source e.g. 1999;2000;2001;2002

Then, open a new query. Add the table and select the fields. Switch to SQL View and type the references to the combo box with a Where clause (using the correct form name, put the form name in square brackets if it contains spaces) as follows:-

SELECT ...
FROM ...
WHERE Year([Start Date]) = forms!formName!cboLeaveYear or Year([End Date])=forms!formName!cboLeaveYear

The Where clause must be put after the From clause but before the Order By clause if there is one. Save the query as qryLeaveDates.


Lastly, in the Properties sheet, put a line of code in the On Click Event of the combo box:-
-----------------------
Private Sub cboLeaveYear_Click()

DoCmd.OpenQuery "qryLeaveDates"

End Sub
-----------------------
so that whenever a year is selected in the combo box, the query is run.

The query will return those records whose Start Dates or End Dates fall on the year selected.

 

[Rich]

Wouldn't it be better to select the actual years for the combo from the existing data?

 

[Jon K]

Thanks, Rich. Your suggestion is far better!


To do so, change the Row Source Type property of the combo box to Table/Query.

Type the following Union query in the Row Source of the combo box (using the correct table name):-

Select Distinct year([Start Date]) from [TableName] UNION select year([End Date]) from [TableName]