Need to add static entry to a SQL populated Combo Box

[mdlueck]

One new weird observation I see all of a sudden with this updated query... the Ver column ended up with an odd square box character aka "[]" looking...

Decompile / Compact / Compile did not solve the problem.

Next I went off to search how Microsoft requires empty columns to be used in UNION queries to be specified. Turns out, I was specifying NULL incorrectly. They simply want a pair of empty double quote characters.

Because the structures of tables included in a UNION must be identical, there are two placeholders in the second SELECT statement to represent orders.order_id and orders.emp_id from the first SELECT statement.

NoteThe placeholders must be the same type as the fields that they represent. If the field is a date type, the placeholder should be { / / }. If the field is a character field, the placeholder should be the empty string ("").

Evidently two double quote characters are also valid for NULLing numeric datatype fields.

Now, why that did not show up in the row for the static entry and overwrote a valid record's Ver value... that would be an Access bug. The [] character should have ended up in the row for the statically added pick list entry. (shrug) Working query as follows:

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],-1 AS[rev],"" AS [revdisp],"" AS [ver],"" AS [price] FROM [MSysObjects]
UNION
SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

Thank you LPurvis once again for your valuable suggestion.

 

[LPurvis]

It's apparently showing a, previously, non-printable Ascii character...
It could be data type coersion - but that would be a common occurence during which you wouldn't expect to see this.

I see you've posted in the mean time...
There's nothing wrong with specifying Null as Null.
Perhaps a data type conversion would have helped. :-s

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],-1 AS[rev],NULL AS [revdisp], IIF(1, NULL, CStr('')) AS [ver],NULL AS [price] FROM [MSysObjects]
UNION
SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

That's assuming that it was a Text string it was expecting.
Anyway - must run for today.

Later!

 

[mdlueck]

That's assuming that it was a Text string it was expecting.

The column is an Integer datatype that was being ill treated.

By forcing NULL values into that column, the result was a row coming from the other query involved in the UNION lost its value (1) and received value NULL ([]).

By specifying "" as NULL for the field place holders in the static row, that cleared up the NULL'iation of the valid records's value.

It was as if the UNION operation suddenly jumped to that other record to fill in the Ver value overwriting that record's legit value and putting the NULL there.

Anyway, all is well since I changed to a pair of double quote characters. Documenting the solution on this thread - perhaps someone else will benefit from my finding.

 

[LPurvis]

Well, you've lost Null then really. You've got a ZLS.
However, that is, at least, an actual value - so your combo box can retain that selection (whereas it will lose any Null entered, returning to no selection).
You just need to compare against a ZLS instead of Null for subsequent use.

Just out of interest, coercing that Null value into a Long Integer type doesn't make a difference does it?

i.e. instead of
... IIF(1, NULL, CStr('')) AS [ver], ...
having
... IIF(1, NULL, CLng('')) AS [ver], ...

Cheers.

 

[mdlueck]

WHowever, that is, at least, an actual value - so your combo box can retain that selection (whereas it will lose any Null entered, returning to no selection).

I do not believe you are understanding. The column (ID number) that the Combo Box is bound to does not appear in the Combo Box pick list.

The query builds a visually appealing complex list combining several bits of data together, and I even pretty format the column names and have column names showing up in the pick list.

When I specify for the static entry:

NULL AS [revdisp],NULL AS [ver],NULL AS [price]

Then suddenly the Ver number of one of the entries actually in the table looses its 1 value and instead appears a [] square character.

However when I specify for the static entry:

"" AS [revdisp],"" AS [ver],"" AS [price]

THEN the record found in the database table displays the correct 1 as its value for the Ver column.

Something was going wrong with the UNION when I was specifying NULL as a column place holder, and "" works as expected.

So all the while, the Combo Box has been updating its selection correctly, EVEN with that [] character visually in the pick list entry.

Understand the situation better?

 

[LPurvis]

Yeah I realised after posting that you don't need this value to bind the coombo's value (unless you were making the 5th column the bound one - which would be unusual). But I'm way too pressed for time to start mentioning addendums to my posts at the moment (unless prompted to do so )

None the less, my curiosoty remains in relation to the option of coercing the type of the Null into an Integer as I just described previously.

As a point of interest, as you're not using it for binding, using the ZLS instead of Null will be totally fine. The Column property of a combo returns ZLS for Nulls anyway.

Cheers.

 

[mdlueck]

None the less, my curiosoty remains in relation to the option of coercing the type of the Null into an Integer as I just described previously.

The Combo Box is not putting NULL into an Integer, and Integer is being selected to populate the Combo Box list, and the static entry must supply all columns involved in the UNION. The other side of the UNION is what is selecting the Ver column from the table which that column is of Integer data type.

I am not aware of Combo Box pick list virtual columns having a datatype for them. Do they?

Yeah I realised after posting that you don't need this value to bind the coombo's value

The Combo Box is bound to col #2 of the query [id].

 

[LPurvis]

I understand your scenario. There's no problem.
But you reported an non-printable Ascii character. I was supposing it might have been due to type conversion - and that is we type cast the Null which was being UNIONed with that integer data, it might have prevented that character problem (it might not have as the problem could lie outside type casting).

The "virtual" columns (they're really just columns) of combo's do have a datatype - Strings. But they can be type cast implicitly.
However the query which provides the source for the combo does indeed have distinct data types. And it is that in which type conversion would happen, before we ever get to the combo stage - and hence potentially the character problem mentioned.

i.e. try the alternate IIF statement I suggested, merely out of curiosity. (It might make no difference and you're OK with ZLS anyway.)

Cheers.

 

[mdlueck]

All of that understood, so...

i.e. try the alternate IIF statement I suggested, merely out of curiosity.

I coded up the Ver field (actually it was Integer in VBA. That worked and did not give me the [] character. So I went ahead and mimicked with the correct C*() to match the column in the FE DB temp table. The working results are as follows:

SELECT [id_vendor] AS [ID / Vendor:],[id],[rev],[revdisp] AS [Rev:],[ver] AS [Ver:],[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],-1 AS [rev],IIF(1, NULL, CStr('')) AS [revdisp],IIF(1, NULL, CInt('')) AS [ver],IIF(1, NULL, CCur('')) AS [price] FROM [MSysObjects]
UNION
SELECT t.[id] & ' / ' & t.[vendortitle] AS [id_vendor],t.[id],t.[rev],t.[revdisp],t.[ver],t.[poprice] AS [price] FROM [tmptblqry_quotes] AS t) AS cb
ORDER BY [rev],[ver];

Cleaner since it does not involve as much string concatenations. Thanks for the enhancement!

 

[LPurvis]

You're welcome.
Glad to know it was indeed the reason (and therefore, follow to have been the solution.

 

[mdlueck]

Sigh.... one more observation with this query:

Part Revision / Version are not mandatory fields, for some type of preliminary stage quote. Pre-release uses numbers 1-99, releases use A-ZZ, and pre-pre have a blank revision. So this causes the static entry to end up not the top entry if there is an entry in the list with blank revision.

I thought to play a slight of hands, make my static entry occupy valye -2, allow Nz to make the blanks -1, and all should have sorted perfectly.

After all, the column type in the FE table is an Integer after all.

So I cooked up the following query, and gave it a try in a DAO.QueryDef window...

SELECT [cb].[id_vendor] AS [ID / Vendor:],[cb].[id],[cb].[rev],[cb].[revdisp] AS [Rev:],[cb].[ver] AS [Ver:],[cb].[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],[B][COLOR=Red]-2 AS [rev][/COLOR][/B],IIF(1, NULL, CStr('')) AS [revdisp],IIF(1, NULL, CInt('')) AS [ver],IIF(1, NULL, CCur('')) AS [price] FROM [MSysObjects]
UNION
SELECT [t].[id] & ' / ' & [t].[vendortitle] AS [id_vendor],[t].[id],[B][COLOR=Red]Nz([t].[rev], -1) AS [rev][/COLOR][/B],[t].[revdisp],[t].[ver],[t].[poprice] AS [price] FROM [tmptblqry_quotes] AS [t]) AS [cb]
ORDER BY [cb].[rev],[cb].[ver];

Note, since the previous discussion I had to add [] around the various table and alias names as I was getting random squawks out of DAO.QueryDef UI controls when flipping between SQL and Design View.

And the results I get appear that Access sorts the Integer column from the FE temp table as String data!!!

Can anyone spot a mistake that Access would be recasting the Integer data into Text data?

 

[mdlueck]

Can anyone spot a mistake that Access would be recasting the Integer data into Text data?

Douhhh!!! That was easy... force casting the data as Integer datatype!!!

SELECT [cb].[id_vendor] AS [ID / Vendor:],[cb].[id],[cb].[rev],[cb].[revdisp] AS [Rev:],[cb].[ver] AS [Ver:],[cb].[price] AS [Price:]
FROM (SELECT TOP 1 "Production Part - Refers to JDE" AS [id_vendor],-1 AS [id],[B][COLOR=Red]CInt(-2) AS [rev][/COLOR][/B],IIF(1, NULL, CStr('')) AS [revdisp],IIF(1, NULL, CInt('')) AS [ver],IIF(1, NULL, CCur('')) AS [price] FROM [MSysObjects]
UNION
SELECT [t].[id] & ' / ' & [t].[vendortitle] AS [id_vendor],[t].[id],[B][COLOR=Red]CInt(Nz([t].[rev], -1)) AS [rev][/COLOR][/B],[t].[revdisp],[t].[ver],[t].[poprice] AS [price] FROM [tmptblqry_quotes] AS [t]) AS [cb]
ORDER BY [cb].[rev],[cb].[ver],[cb].[id_vendor];