Replacing "null" with "(default") in a combobox

[Maury Markowitz]

I have a combobox bound to a nullable varchar field in SQL Server.
Null means something specific, "use the default logic". I'd like to
have the system display "(default)" in the combobox if the value in
the db is null. I tried this:

SELECT null as [ID], '(default)' as programId UNION SELECT programId
as [ID], programId FROM tblPrograms ORDER BY programId

and then bound to column 1 and hid it. However, when you select
"(default)" the value disappears after you select it.

Am I overthinking this? Is there an easier solution?

Maury

 

[Jeff Boyce]

Maury

Are you using two different definitions of "Null".

In Access (and SQL Server), "Null" means "nothing there, nothing ever been
there, no way to compare to something that isn't there".

It sounds like you're using "null" to mean something else...

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Justin]

Use the IIF function in the combo box row source query.

IIF(IsNull([My_Field]),"Default",[My_Field])

 

[Allen Browne]

One way to do this would be to place a text box on top of the combo
(slightly narrower, so the combo's drop-down arrow still shows), and set its
Control Source to something like this:
=IIf([Combo0] Is Null,"(default)",[Combo0])

The text box's TabStop will be No, and its On Enter event will SetFocus to
the combo (for when someone clicks on it.)

If these were numeric values (not varchars), you could do it without the
text box by setting the combo's Format to:
#;-#;0;"(default)"

Either way, the "(default)" disappears when the combo gets focus.

 

[Sylvain Lafontaine]

Instead of using Null, an easier solution would be to use a negative ID:

SELECT -1 as [ID], '(default)' as programId UNION SELECT programId
as [ID], programId FROM tblPrograms ORDER BY programId

Not only this will solve many problems but it will also give you the
opportunity to have more than one special action.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)