Combo Box with "All" Selection 1

[Tigerlili3]

I have a combo box with the following as the rowsource:

SELECT tblState.StateAb, tblState.StateName FROM tblState UNION SELECT [COLOR=#ff0000]""[/color],"(All States)"  FROM tblState ORDER BY tblState.StateName;

This rowsource takes the values that are in tblState and adds "(All States)" to the top of the list. The first column (StateAb) is the bound column. The column widths are set at 0";1" so that only the state name shows in the combo box.

I need the stored value for the "(All States)" selection to be null. That's why I have the double quotes for the first column in the UNION query. "(All States)" shows up inthe combo box as a choice, however, if I select it, the combo box looks blank as if I have selected nothing. If I put a the letter A in doubles quotes in place of the red double quotes in the UNION query and select "(All States)" from the combo box, "(All States)" shows up as the selection. I don't want to store a value with the "(All States)" selection. Does anyone know how leave the first column value null, but show "(All States)" in the combo box if I have selected it?

Thanks!

 

[misscrf]

did you try:

SELECT tblState.StateAb, tblState.StateName FROM tblState UNION SELECT "","","(All States)" FROM tblState ORDER BY tblState.StateName;


probably won't work, but might try it.


misscrf

Management is doing things right, leadership is doing the right things

 

[Tigerlili3]

This suggestion creates an error because the query only requires two columns. The second set of quotation marks at the beginning indicates that there would be a third column (which there is not).

Explain SQL: The way the SQL query works is as follows: Each item between the SELECT and the FROM represents a column in the query. Since I only need two columns: StateAb and StateName, I only list two items after UNION SELECT.

Restate question: The problem I run into is when I choose to have the first one of these values in the query set to null. When I do this, and choose "(All States)" from the combo box, the box appears as though I have selected nothing. If I set the first value to something other than null and choose "(All States)" from the combo box, the box will show that I have selected "(All States)". Why will it not show that I have selected all states when the stored value is null?

 

[FancyPrairie]

You're not storing a NULL you're storing a null string. Even if you could store a Null, your SQL statement would then be looking for a NULL. The code that builds the SQL statement is going to have to check for the null string. If it cointains a null string, then don't include the Where Clause. Something like this:

if (len(YourComboBox.value) = 0) then
strSQL = "Select * From ..."
Else
strSQL = "Select * From Where strState = '" & YourComboVox.value & "';"
End If

 

[misscrf]

Thats the ticket! I was getting confused because I didn't understand the null thing. This sounds good to me, Tiger.

Great work fancy

misscrf

Management is doing things right, leadership is doing the right things

 

[Tigerlili3]

My problem is not with "looking for" or using the value after I have stored it!! The problem is simply the display of text in the combo box when the selection has been made. I want the box to display "(All States)" if I have selected "(All States)". My combo box has two columns-- StateAb and StateName. If the SQL query that is the rowsource for my query says:

SELECT tblState.StateAb, tblState.StateName FROM tblState UNION SELECT "ALL","(All States)"  FROM tblState
ORDER BY tblState.StateName;

then the source for the combo box will look like this:

StateAb StateName
ALL (All States)
AL Alabama
AK Alaska
AZ Arizona
AR Arkansas
CA California
CO Colorado
CT Connecticut
DE Delaware
DC District of Columbia
FL Florida

Because I have set the first column of the combo box to be hidden, the StateAb column does not show in the combo box.

If the SQL query that is the rowsource for my query says:

SELECT tblState.StateAb, tblState.StateName FROM tblState UNION SELECT "","(All States)"  FROM tblState
ORDER BY tblState.StateName;

then the source for the combo box will look like this:

StateAb StateName
(All States)
AL Alabama
AK Alaska
AZ Arizona
AR Arkansas
CA California
CO Colorado
CT Connecticut
DE Delaware
DC District of Columbia
FL Florida

My question is: Why will Access not show "(All States)" in the combo box when I use the second list as the rowsource for the combo box vs the first list?

This question is not on how to use the value, it is on how to display the value in the combo box in which it was selected.

 

[FancyPrairie]

I understand. Instead of "" use " ". Like this:

SELECT tblState.StateAb, tblState.StateName FROM tblState UNION SELECT " ","(All States)" FROM tblState ORDER BY tblState.StateName;

 

[Tigerlili3]

Two things...

First--The suggestion of using " " instead of "" will cause the value to be displayed but will pass a value of " " instead of "" to a query when I reference the value of the combo box.

I have decided to use "ALL" and program an if statement in my VBA that creates the where clause of the SQL query for the next step in my process.

Second--Who has been awarding stars in this short-lived post??? I have not.