Filet Records by Current Year

[jbarrum]

I have a table that holds customer orders. It is linked to a table that holds years. The [years] table has one field "years" and it is the PK.
The orders table has the FK "years". Every customer can only have one order per year.

The field "year" in the orders table holds a 4 digit value (ie. 2009). I would like to create a Qry that would show me only orders in the current year so that next year my orders from 2009 no longer show in the Qry.

Can this be done?

[NTC]

in your query you take a blank column and create a calculated value based on the current date - - -

to make a quick & dirty test - make a query, add any table, add any one field and just put this into a blank column next to it:

CurrentYear: Year(Now())

you will get a 2 column result with the field you added and the field next to it will be all 2009 in every row...it will change automatically to 2010 next year....

[jbarrum]

Hello NTC,

thanks for the reply. This worked perfectly. I have one more challenge to overcome and I think my DB will be complete. I have a report that is summing all values in one field. This calculate field is in the group footer. I need to be able to sort the records on the report based on this calculated value. Do you know if this can be done?

[dcrake]

You are going to run into a brick wall if you are not careful. Lets say you do use the Year(Now()) functionality but what about in early January 2010 when you want to run reports for 2009 it won't find them will it.

Sorry.

David

[Rainlover]

The field "year" in the orders table

Year is a reserved word.

Do not use.

[jbarrum]

I know, by using this metod I was able to get a colume that holds the value of the current year. From there I was able to create a third calculated column that uses and IIF statement. IIF the two columns = then "0" , "1". At this piont I am able to filter on this colum all records with the value of "0". This gives me all records in the current year. Next year the column calculationg the current year will change giving me new records in my query. Are there any issues with this method?

[jbarrum]

good point. I did not think of that. I will change the field name.

Thanks