Asset notifications

[bigzos]

I have a database of asset information with asset age as one of the columns..now i wish to display all the assets which crossed 4 years in the current year ..any suggestions will be appreciated!

[Oxiegen]

Depending on the format of the age column, you can limit the results by checking to see if the age is equal to or greater than the current year - 4.

Ex if column is DateTime/SmallDateTime datatype:
SELECT * FROM <table> WHERE YEAR(<asset age column>) >= 2009

[Reverend Jim]

To make it more general you could replace

= 2009

with

DATEPART("YEAR",GETDATE())-3

[bigzos]

this query will show even the assets which are 5 0r 6 year old i.e. the assets which crossed 4 years two years or 3 years back
I just want those assets which just crossed 4 years(in the current year)

[Reverend Jim]

The how about

SELECT * FROM <table> WHERE DATEPART("YEAR",GETDATE()) - YEAR(<asset age column>) = 3

[bigzos]

I have a <PO date> column i.e. purchase order date
I tried the query
select * from table where '"&datediff("yyyy",now(),PO date)&"'=3 but it wont work
actually,I am not able to use PO date column in the function
it is a datetimepicker one in vb and date/time type in access database

[Oxiegen]

Try this:

SELECT * FROM table WHERE DateDiff(year, Now(), '" & <po date datetimepicker>.Value & " 00:00:00.0000000') = 3".

[poojavb]

What kind of database is it?? Access, MSSQL or MYSQL?

I derived a query in Access
hope ur colname is a datetime field

SELECT *
FROM tablename
WHERE (format(colname,"yyyy")<Year(Now())-4);

[bigzos]

I chose to use the query for listing assets whose asset age is 4 as I am already using a query for calculating age from the purchase till now.