Just show year in a date column 1

[DougP]

I want to be able to put a combo box on my form and have it just show the Years we have done projects. Then pass that to a Subform.

here is the SQL code for the combo box

SELECT Projects.Date
FROM Projects
GROUP BY Projects.Date
ORDER BY Projects.Date;

Also I just need the to be able to return records that were done in only that year.

TIA

DougP, MCP, A+

I love this site and all you folks that helped me over the years!

 

[lespaul]

This will show you both the date and the year:

SELECT P.Date, YEAR(P.Date) FROM Projects

Unless your using an aggregate function (MAX, MIN, SUM, AVG) you don't need to use the GROUP BY clause.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[DougP]

Great thanks you
The reason I am using a Group by is so I don't see the year from every record. I just want to see one occurance of each year.
This is in a combo box and I just want to see years that we did a project for a particular Company.

1995
1996
1999
2001
2002
2005
etc

DougP, MCP, A+

I love this site and all you folks that helped me over the years!

 

[AlexCuse]

>>>The reason I am using a Group by is so I don't see the year from every record. I just want to see one occurance of each year.

You might have a look in the help files at select distinct. I'm not sure if you'll gain any efficiency, but I've found its' good practice to only use group by when you have aggregates involved, and distinct otherwise. It makes queries easier to read.

Good Luck

Alex

Ignorance of certain subjects is a great part of wisdom

 

[DougP]

thanks AlexCuse,
I changed it to this.

SELECT DISTINCT Year([Date]) AS TheYear FROM Projects ORDER BY Year([Date]);

It did not seem to speed it up. Either one takes a few seconds to bring back the results. There are some 23,000 records it's looking through.

But I think you are correct, it's a better way to do it.



DougP, MCP, A+

I love this site and all you folks that helped me over the years!