[AccessD] Empty Field Label

Hi Rocky

One thing to remember is that for combo- and listboxes everything is strings. Thus Null turns into an empty string, "".
You could try with that. Or set the default value for the combox to "0" and keep "0" as the value for "Client ID".

/gustav

>>> rockysmolin at bchacc.com 10-04-2009 22:01:02 >>>
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