Double Trouble with Alias Columns & Nested Case When in APD/SQLDatabase

[pete.trudell]

Morning, I have been working on converting, by hand, a Access 2003
standalone database to a Access 2007 adp/SQL database. I've had
considerable luck , and assistance from this site, in almost
completing my project. However, I have run into two road blocks in my
query writing:

1. How to reference an Alias column in the same query as that Alias
is created. For example I have a field DateDiff('yy',NLGD,SepCh) As
SepYr. I then need to reference SepYr to compare in a Case When
statement. Program errs out.

2. How then do I nest Case When statements: in 2003 I used IIf
([SepYr]>=35,21,IIf([SepYr]>=30,19,IIf([SepYr]>=25,17, etc.
etc. ))))). However, I can not finmd the proper way to nest Case When
Rhen Else End.

Appreciate any directions on this.

PS: I already attempted to find a way to creat the alias in query and
then use that as with 2003, but ADP/SQL does not allow access to
quries as with tables ??

Thanks

 

[KARL DEWEY]

Your best bet is to not attempt to use the alias in the same query that
generates it. Use the same calculations instead.
The reason is the chicken and egg dilemma - wich came first. If the SQL
tries to procees data using the alais before it is created then it is lost.
In my experience, but I would not put much on it, SQL seems to be processed
from last to first in the SQL statement. So if you put the alais part near
the end and then it's use up front near the SELECT it might work.