PeterBaileyUk
asked on
dateadd in querygrid
I have a criteria of:
>=DateAdd("y",-5,Now()) And <=Now()
in a field called [smmtyear]
i had hoped that my criteria would give me the last 5 years of date ie 2009, 2008, 2007, 2006 and 2005
OR the max 5 of years
data could be 2009, 2007, 2006, 2005, 2004... ie in the event that there were no records for 2008 as in this example.
regards
I failed miserably as no rows are returned
>=DateAdd("y",-5,Now()) And <=Now()
in a field called [smmtyear]
i had hoped that my criteria would give me the last 5 years of date ie 2009, 2008, 2007, 2006 and 2005
OR the max 5 of years
data could be 2009, 2007, 2006, 2005, 2004... ie in the event that there were no records for 2008 as in this example.
regards
I failed miserably as no rows are returned
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
[smmtyear] is a string how do i resplve that, it says data type mismatch in query expression
ASKER
that should say resolve sorry typo
you didnt specified that the query will work on access... so try this
YEAR(smmtyea) BETWEEN YEAR(NOW()) -5 AND YEAR(NOW())
YEAR(smmtyea) BETWEEN YEAR(NOW()) -5 AND YEAR(NOW())
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can not depend on a query like "select top 5 ...... on [smmtyear]" that may produce the result you wanted but it does not guaranteed that the result will always be as you expected... You are depending on data changes..
ASKER