Format Date Field

[nigelh]

I am trying to code a generic query that will use a year provided by the user on a combo box. The user will select a year (I hope to do this for months as well) then the query will run using the between function.
My problem is supplying the dates for the 'Between' statement.

Here is one of my many attempts (please don't laugh).

Between #01/01/(format(Forms![FrmQryTotalHolsTakenLeft]![Combo18],"yy"))# and #31/12/(format(Forms![FrmQryTotalHolsTakenLeft]![Combo18],"yy"))#

I'd be grateful for any help.

Cheers,

NH

 

[Rich]

Why don't you use the calendar control to select dates, it's much easier. Look up the Solutions database supplied by MS for examples.
HTH

 

[nigelh]

Thanks Rich, but its not the selection of the date I'm concerned about, its passing a year or month to to a query.

Cheers

 

[Rich]

Look up the Date Part function for examples

 

[nigelh]

Thanks Rich,

I've worked out the Date & Format functions (I think). However I'm still struggling, I'll try and explain what I'm attempting to do more clearly.
I have set up an employee database for my department. I have a form FrmStaffEdit which contains name, date joined, holiday entitlement etc.. Another form FrmStaffDetail is as FrmStaffEdit with 2 subforms FrmQryTotalHolsTakenLeft & FrmQryTotalSick (both based on queries).
When FrmStaffDetail is opened the subforms will display details for the current year, however I'm looking for a method to select a different year within the subforms and the details for that year to be updtaed automatically.
I hope this makes sense and thanks again for your advice so far.

NH