[AccessD] Empty Field Label

Gustav:

I changed the row source to:

SELECT ClientID, ClientCode, ClientName, 1 AS SortField FROM Clients UNION
SELECT TOP 1 "" AS ClientID, "Client ID" AS ClientCode, "" AS ClientName, 0
AS SortField FROM Clients
ORDER BY SortField, ClientCode;
 
But the records with no client selected still do not show "Client ID".  It
shows when I run the query - right at the top.  But it doesn't show at the
top of the list in the combo box when I drop the list down.  Maybe that's
why it doesn't show that first record in the combo box for records for which
a client has not been selected.  But why wouldn't it show in the list if it
shows in the query?

I just remembered to change the number of columns from 3 to 4 and make the
last column width zero - but that didn't help.

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 1:29 PM
To: accessd at databaseadvisors.com
Subject: Re: [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


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