[AccessD] Empty Field Label

Thanks Gustav.  Works perfectly in the query.  

But records without a client ID selected don't show "Client ID" in the combo
box.  I changed the "0" to Null in the Union query on the theory that those
record have a null in the record. But no soap.  DO you know why that "Client
ID" might not be showing up in the combo box?

Thanks and regards,

Rocky



 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, April 10, 2009 10:53 AM
To: accessd at databaseadvisors.com
Subject: Re: [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



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com