Problem getting descending order in select statement to work

[aussie_user]

I have the following lookup statement

SELECT DISTINCTROW [Year] FROM [Year] ORDER BY [Year DESC];

I have a PROJECT Table that I need to indicate the year that the project commenced. Once all of the historical data has been input, the most frequent year used will be current year. My YEAR Table consists of one Field that includes all of the years that had projects. If I use this statement, an error box pops up the first time I try and use the dropdown box. The years are showing in Descending Order. If I remove the DESC then no error box pops up but the years appear in ascending order and the user has to scroll down to find the current year. The same problem occurs when I create input forms. Is there something wrong with my statement or something different that needs to be done to get the dropdown list to appear in reverse (descending) order?

Thanks for any help.

 

[Tim K.]

SELECT DISTINCTROW [Year] FROM [Year] ORDER BY [Year] DESC;

 

[Pat Hartman]

I really hope that your field is not actually named Year.

 

[aussie_user]

Thanks Tim, from the help it appeared that the DESC would be inside the brackets. It works fine now.

I take it Year is a bad name to use, thanks for the warning. I'm still working on building the database so it is not to late to change the name.

 

[Pat Hartman]

You need to avoid using the names of Methods/Properties/commands/functions that are ingegral to Access and SQL. Jet is pretty flexible in what it allows. However, if you try to use these names in VBA you will run into strange problems because of the confusion they cause. Every programming language looks through its own objects first when trying to execute code. So, VBA would interpret Year as its Year function and not as your field name. Also, do NOT use embedded spaces or any special characters in your names. Stick to letters, numbers, and the underscore (_). CustNum and Cust_Num are acceptable. Cust # is not.

In summary. Start every name with a letter. Use proper case or the underscore to separate words. Limit your names to 30 characters. Do not use space or special characters. Avoid simple non-descriptive class type words such as Year, Summary, Name, etc. They are probably already taken by some aspect of the development environment.