?
Solved

dateadd in querygrid

Posted on 2009-12-29
8
Medium Priority
?
325 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:PeterBaileyUk
  • 4
  • 3
8 Comments
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 1000 total points
ID: 26136759
use

YEAR(smmtyea) BETWEEN YEAR(GETDATE()) -5 AND YEAR(GETDATE())
0
 

Author Comment

by:PeterBaileyUk
ID: 26136806
it says undefined function getdate, i am using this in ms access 2007 in the query grid
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1000 total points
ID: 26136840



Format(smmtyea,"mmmm") BETWEEN Format(NOw(),"mmmm") -5 AND Format(NOw(),"mmmm")
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:PeterBaileyUk
ID: 26136862
[smmtyear] is a string how do i resplve that, it says data type mismatch in query expression
0
 

Author Comment

by:PeterBaileyUk
ID: 26136888
that should say resolve sorry typo
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26136899
you didnt specified that the query will work on access... so try this

YEAR(smmtyea) BETWEEN YEAR(NOW()) -5 AND YEAR(NOW())
0
 

Accepted Solution

by:
PeterBaileyUk earned 0 total points
ID: 26136986
Eureka

ok I used select top 5 ...... on [smmtyear] and its done the same as I wanted.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26137001
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..
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question