Sorting derived colums

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

The first SQL statement works the second does not. It prompts me for a
parameter.

How can I get it to sort by the FYCForAgent column??

SELECT PolicyNumber, AgentID, iif(IsNull(AgentID), '0', AgentID) AS
FYCForAgent
FROM tblPolicyStatus
ORDER BY AgentID;

SELECT PolicyNumber, AgentID, iif(IsNull(AgentID), '0', AgentID) AS
FYCForAgent
FROM tblPolicyStatus
ORDER BY FYCForAgent;


Thanks
 
From what I understand, the second one will work in SQL server; but you're
right, it doesn't work in Access/Jet. Remove the Order By clause then open
the query in Design View (the query grid view) then set the Sort in the
grid. Next, go back to SQL view and see how Access wrote it. I believe what
you'll find is:

SELECT PolicyNumber, AgentID, iif(IsNull(AgentID), '0', AgentID) AS
FYCForAgent
FROM tblPolicyStatus
ORDER BY iif(IsNull(AgentID), '0', AgentID);
 
Hi, Craig.

One can also use the ordinal column to identify the column to be sorted, not
just the column name. Since the column "Alias" or "calculated field" is the
third column in the query, try:

SELECT PolicyNumber, AgentID, iif(IsNull(AgentID), '0', AgentID) AS
FYCForAgent
FROM tblPolicyStatus
ORDER BY 3;

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Gee...that is one cool tip......

I always thought you had to either:

Repeat the whole expression in the sort.

or

Save the sql...and use the query on query idea as suggested by Arvin..
 
Hi, Albert.

Thanks. I learned that cool trick from an experienced Oracle DBA!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


:

Gee...that is one cool tip......

I always thought you had to either:

Repeat the whole expression in the sort.

or

Save the sql...and use the query on query idea as suggested by Arvin..
 
Back
Top