Query to search for Certain Years

[hawkins]

I am creating a feeder status report and the feeders have static information and changing information depending on the year. I have a static table called Static, which has a list of the feeder names and static information about them. I also have a table called Yearly which holds information by the year and is referred to the Static table by the feeder name. I was wonidering how I could set up a query in the report to search for the current year value out of the Yearly table say I want the CalcGrwth (Column Name) value for a specific feeder name. I created the base report using the wizard so that the feeder name changes and I want to correspond that name to the year. Similar Id want the previous year as well. I have done this by using the report wizard but it is not in the format I would like. I am new to access so detailed explanations would be greatly appreciated. Thank you for your help and any suggestions are welcome!

[boblarson]

What does the field that has the date information look like? If it is a full date then you can create your own field in the query for year and then have the criteria like this.

First, the field in the query - so in the QBE grid you would have:

CalcGrwthYear:Year([FieldWithDateInfoInIt])

and the criteria for the current year would simply be:

Year(Date())

Then the criteria for the previous year

Year(Date())-1

[hawkins]

Thank you for the response, the year field is only the year, for example 2010, I tried to do CalcGrwthYear:Year([YearRecorded]) in a QBE field and then criteria I used Year(Now()) and Year(Date()) but I did not recieve any results even though there are years of 2011 in my table.

[boblarson]

If the Year field is only the year then you wouldn't need to create a separate field and you could just use

Year(Date())

as the criteria on that field. But, if that Year field [YearRecorded] is text then you would have to use

Cstr(Year(Date()))

as the criteria.

[hawkins]

Thank you, that worked perfect! I was also wondering how to make sure the query results only include the name given in the header of the report (sorted by name).

[boblarson]

I was also wondering how to make sure the query results only include the name given in the header of the report (sorted by name).

Not sure I understand what you're asking for. Please explain a bit more in detail what you are looking to do with that, how it currently works, etc.

[hawkins]

I have attached a file of my database. I am just trying to 'screw' around on this one so I dont mess up my bigger one I have been working on. I am trying to create a page report for each of the feeder name. Each feeder has static and yearly information. The report now is organized by feeder name and I want to use the query to be able to get yearly data for each year so I can then organize it the way I would like it to look. As of right now, my query runs how I would like to but in the report it does not show any results as it did when I ran the query. Also I want the results to this query (when it is able to work) to be based upon the feeder name for that page.

[hawkins]

thanks for the help, I finally figured it out by adding [Reports]![Static]![Name] to the criteria of the name.