Annual report (between dates)

**jik_ff** · 11-08-2011, 04:16 PM

I'm trying to run anual reports. I have a query setup, and I need to request the Year that the report needs to check on. For now I have been using this:

Between [Start of Year : like 01/01/2011] And [End of Year : like 12/31/2011]

as the criteria. I would prefer to just have them enter the year (such as 2012) and then it would use that as the criteria. Any help on how I can build this in the criteria?

**ConneXionLost** · 11-08-2011, 04:32 PM

Set up a calculated field in your query as

Please Login or Register  to view this content.

then use it for your year (2012) criteria.

Cheers,

**jik_ff** · 11-08-2011, 04:41 PM

I guess my issue is that the data has a date field. I can't really compare a date to a year... can I?

**davegugg** · 11-08-2011, 04:46 PM

Assuming you are capturing the requested year as a variable, say intYear:

SELECT * FROM yourTable WHERE Year(dateField) = [intYear]

**jik_ff** · 11-08-2011, 05:29 PM

Can I use that statement in the criteria?

Right now I have a filed called EOL DATE which poles 2 fields from the original table. The data (put together by a different group) has the date in 1 of 2 fields (depending on if the equipment is purchased or leased). So my EOL DATE field in my query:
EOL DATE: IIf(Not IsNull([dbo_AM_Assets].[SCHEDULED_RETURN_DATE]),[dbo_AM_Assets].[SCHEDULED_RETURN_DATE],[dbo_AM_Assets].[SUGGESTED_REFRESH_DATE])

gives back 1 of these dates. I'm just trying to get the flow of how to do this. So I need to make a new field in the query? or just criteria? I'm starting to get confused.

EDIT:
Ok, I got it. I made a new query field like this:
YEAR: Year(IIf(Not IsNull([dbo_AM_Assets].[SCHEDULED_RETURN_DATE]),[dbo_AM_Assets].[SCHEDULED_RETURN_DATE],[dbo_AM_Assets].[SUGGESTED_REFRESH_DATE]))

Pretty much the same as my date field, but then narrowed it to Year(). Thanks for the help