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?
Set up a calculated field in your query as
then use it for your year (2012) criteria.DateYear: Year([YourDateField])
Cheers,
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
I guess my issue is that the data has a date field. I can't really compare a date to a year... can I?
Assuming you are capturing the requested year as a variable, say intYear:
SELECT * FROM yourTable WHERE Year(dateField) = [intYear]
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
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
Last edited by jik_ff; 11-08-2011 at 04:35 PM.
In the WHERE clause, you should just be able to add:
WHERE Year([EOL DATE]) = intYear
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks