[AccessD] Empty Field Label

Hi Rocky

You can add a field to order by which sets you in full control without using "smart" values or codes:

SELECT 
  ClientID, 
  ClientCode, 
  ClientName,
  1 AS SortField 
FROM 
  Clients
UNION 
SELECT TOP 1
  "0" AS ClientID,
  "Client ID" AS ClientCode,
  "" AS ClientName,
  0 AS SortField 
FROM 
  Clients 
ORDER BY 
  SortField,
  ClientCode;

/gustav


>>> rockysmolin at bchacc.com 10-04-2009 19:18:56 >>>
Jim:

The original query has an ORDER BY clause which I need to get the combo to
come out correctly.  

Select Clients.ClientID, Clients.ClientCode, Clients.ClientName FROM Clients
ORDER BY Clients.ClientCode;

I modified the query:

SELECT "0","Client ID","" FROM Clients UNION Select Clients.ClientID,
Clients.ClientCode, Clients.ClientName
FROM Clients ORDER BY Clients.ClientCode;

And got an error when I ran it - the ORDER BY expression
(Clients.ClientCode) includes fields that are not selected by the query.
Etc.

So I changed it to:

Select Clients.ClientID, Clients.ClientCode, Clients.ClientName FROM Clients
UNION SELECT "0","Client ID","" FROM Clients ORDER BY Clients.ClientCode;

And that worked except that the "Client ID" didn't come out on top.  So I
put a space in front:

Select Clients.ClientID, Clients.ClientCode, Clients.ClientName FROM Clients
UNION SELECT "0"," Client ID","" FROM Clients ORDER BY Clients.ClientCode;

And that works when I run it as a query.

But that first record 0,"Client ID" doesn't show up in the combo box.  

It does show up if I put the SELECT "0","Client ID","" UNION ... first but
then I can't use the ORDER BY.  Any way to finesse that one?

Thanks.


Rocky