Simple criteria on query

[setis]

I am trying to apply a criteria on a query with a list of dates.



I want to extract the year from a form and I want to see the dates for that year on the query results.



I am trying in the criteria for the date: Year([Forms]![Main Menu]![DatSav1])


The query results are empty.

 

[Gasman]

It would probably be easier to extract the year of the date in question as an extra field and then use that as your criteri for that field.?


RequiredYear:Year(YourDate)


HTH

 

[Ranman256]

your query should pull the items you need, the date and an extra field: YR: Year([DateFld])
THEN filter your query on:
Yr=Yr([Forms]![Main Menu]![DatSav1])

or just make the box DatSav1 show the year only, then:
Yr=[Forms]![Main Menu]![DatSav1]

 

[setis]

It would probably be easier to extract the year of the date in question as an extra field and then use that as your criteri for that field.?


RequiredYear:Year(YourDate)


HTH

For some reason, when I do an extra field in the query Yr: [Forms]![Main Menu]![DatSav1] the results of the Yr column are all blank..


If I create an extra textbox in [MainMenu] YearSaving=Year([DatSav1]) I get the year in that one..

 

[Pat Hartman]

Where Year(yourtablefield) = Year(Forms!yourform!yourformfield)

 

[Galaxiom]

Where Year(yourtablefield) = Year(Forms!yourform!yourformfield)

Extracting the year from a date on every record is simple but horrendously inefficient because the function must be applied to every record and an index cannot be used. You won't notice at first but the query will become slower and slower as the number of records increases.

The following will be easily one hundred times faster.

WHERE datefield BETWEEN DateSerial(Forms!formname.textyear,1,1) AND DateSerial(Forms!formname.textyear,12,31)

Index the datefield in the table.

 

[setis]

WHERE datefield BETWEEN DateSerial(Forms!formname.textyear,1,1) AND DateSerial(Forms!formname.textyear,12,31)

Index the datefield in the table.

Thank you very much Pat and Galaxiom for your answers... this looks so right, but still there must be something that I'm doing wrong! My query is coming out empty



My criteria looks like this:
"WHERE [DateInvoiceReceived]" Between DateSerial([Forms]![Main Menu].[YearSaving];1;1) And DateSerial([Forms]![Main Menu].[YearSaving];12;31)


Note: In my system the function separators are ";" instead of ","




Just for context, in the [MainMenu] form I have to textboxes for a date interval that I use in another query to report cases between those dates.
What I'm trying to do is to be able to report a list o cases for the whole year and I am extracting the year on another textbox [YearSaving] (from the first textbox date) in [MainMenu] .

 

[setis]

I finally made it work using one of the first suggestions. I created an expresion in the query extracting the year and a criteria to match the year from the desired date fiel.



Thanks all for your help!