Combo Box doesn't show selection. Why?

[OldBirdman]

Access 2003 Windows XP
I have a combo box on a my form fOptions named cboSortOrder
Relevant properties are:
Name: cboSortOrder
Row Source Type: = Value List
Row Source : "";"Ascending","ORDER BY DESC","Descending"
Bound Column : 1
Column Count: 2
Column Widths: 0";2"

I intended to write code such as:

sqlShow = sqlSELECT & sqlFROM & sqlWHERE & [cboSortOrder] & ";"

If I choose 'Descending' in my combo box, then the combo box displays 'Descending' and in the Immediate Pane ?Forms!fOptions.cboSortOrder is: ORDER BY DESC
But if I choose 'Ascending', then the combo box displays a blank/empty and ?Forms!fOptions.cboSortOrder is: Null

Why is this result Null? It is clearly set to a zero-length string in Row Source and a zero-length string is not Null.
Why doesn't the word 'Ascending' appear in the combo box when selected?

I am NOT looking for a fix, but for understanding. I can work around this. In the above example, replacing "" with " " in Row Source makes everything work as expected, with the current Jet Engine. Other places not so simple, but I can use numeric values in column 1 (Bound) and If ... then ... else statements to build my SQL Statements.

I was trying to simplify my code, but I don't like to rely on other programs (Jet) to work correctly in the future with non-perfect data.

[Mariostg]

Your problem has to do with the column count. Try 1 instead...

[Stewart Ross]

Hi OB. The syntax for what you want to do with the combo's value is slightly out, in that the ORDER BY clause in SQL should be

ORDER BY [YourFieldName]
or
ORDER BY [YourFieldName] ASC (the ASC tag is assumed if left off)
or
ORDER BY [YourFieldName] DESC

If you wish to construct a SQL string using the combo's value to select the sort order you'll need to specify the field you are ordering on as part of the ORDER BY clause.

-Stewart

[OldBirdman]

For properties, columns are referred to as one-based, so 2 columns is correct. If referencing in VBA code, then columns are zero-based, and so I would have column(0) and column(1).

[Mariostg]

Then what you want is:

Row Source : "ORDER BY ASC";"Ascending","ORDER BY DESC","Descending"
 

[OldBirdman]

Stewart -
You're correct, I forgot the field name in the ORDER BY clause in my example. This was my error in moving the essence of the problem from a database of 20MB size to this forum.

Mariostg -
In my original post, I ask:

Why is this result Null? It is clearly set to a zero-length string in Row Source and a zero-length string is not Null.
Why doesn't the word 'Ascending' appear in the combo box when selected?

There are many work-arounds for this example, fewer if this is a WHERE clause instead of an ORDER BY.
If one of my choices is a zero-length string, for whatever reason of logic of my program, why can't I have that in a combo box?

[Mariostg]

OldBridMan,
If I understand correctly, if you select Descending, you want to build a clause that has "Order By Desc" in it. While if you select Ascending, you don't do anything special with the clause as far as ordering goes. But yet when you select Ascending, you see a blank as opposed to see Ascending.
I tried it and although I cannot explain why, if you put a space between your empty double quotes, it works fine i.e. the word Ascending will appear in the selection.

[OldBirdman]

Let me try again. I don't care about the SQL string. My question is about the combo box. I only included the SQL to show what I was doing with the combo box. My original post says:

I am NOT looking for a fix, but for understanding. I can work around this.

So OK, new example. I want a combo box that has the numbers 0 through 9, and when I select one of these numbers, I want the combo box to have as its value a string with the length selected, filled with the number selected. Select 2 and the combo box would have as its value "22". Select 8 and result would be "88888888". I'm not going to say why I want this, because I don't want a fix. I want to know why a Row Source of

"";"0";"1";"1";"22";"2";"333";"3"";"4444";"4"";"55555";"5"";"666666";"6"";"7777777";"7"";"88888888";"8";"999999999";"9"

doesn't work, if all other properties are as stated in my original post.
Doesn't work how? If I select 1, 2, ... thru 9, it works. The number is displayed in the combo box, and its value is a string. If I select 0, the combo box appears empty, as if nothing is selected. The combo box's value is Null, not the zero-length string "".
My question then is: How can I have a zero-length string as a value for a combo box?

[Stewart Ross]

Short answer is that you cannot return an empty string from a value list. The null value returned reflects the absence of a value between the delimiters in the list element concerned. I would speculate that the developers did not have in mind empty strings as possible values in a value list.

To accomplish what you want you can use the Nz function on the value of the combo. This will return an empty string in place of the null when that row is selected, leaving the other value list entries that are non-null unaffected:

SomeValue = Nz(YourCombo)

Re your comment on the SQL part not being the point of your question, we'd not be helping other posters who might be taking different things than you expect from reading your question if we did not point out that the ordering example given would not quite work as posted. I accept that it was not the point of your question, however.

-Stewart

[OldBirdman]

Curiouser and curiouser. Short answer is "I can't", but what is going on?
If I change to a table (2 rows, 2 fields) with the same data as the value list, the combo box can have a zero-length string. This is different from a Value List, which cannot put a "" into the .Value property. Choosing a row with a zero-length string results in the displayed text (text box portion of combo box) being set to a zero-length string.
The rows and columns of the combo box have the correct values, with .Column(0,0) being "" even after Access puts a null in the combo's .Value property. I can find no difference at run-time with the row/column of the combo box whether it is from a Table or a Value List.
When a row is selected, Access should assign the .Value property whatever is in the bound column for that row. The display is the .Text property, and that should be assigned from the column shown.
The fact that .Text is set to "" when .Value = "" is weird. Is Accessing testing for this condition and messing it up?
I'll go back to using numbers in the bound column, and lots of Select Case statements in my code.
Thank you for your attention.