Bug Report - Null Causes Font Change In Union Query

  • Thread starter Thread starter T Barron
  • Start date Start date
T

T Barron

Null Causes Font Change In Union Query
The following query will cause a font change that will
result in an unreadable query.

SELECT NULL as Test
FROM Table1
UNION SELECT Table1.Test as Test
FROM Table1;

In my example the Test field is numeric.

This error is a newly added "feature" in the recent
versions and service packs. It did not used to occur.
 
The first SELECT deterimines the data type of each field. Your field is
being interpreted as Text type because it is Null on all rows in the first
SELECT.

Could you reverse the order?
SELECT Table1.Test as Test FROM Table1
UNION
SELECT NULL as Test FROM Table1;

Without testing, you might also get away with explicitly typcasting to text:
SELECT NULL as Test FROM Table1
UNION
SELECT Str(Table1.Test) as Test FROM Table1;
 
PMFJI

the first select in UNION

SELECT NULL as Test FROM Table1

will actually cast the field type as **binary***


to cast as long but return Nulls,

SELECT IIF(True, Null, 0) FROM Table1
UNION
SELECT Table1.Test FROM Table1;

this is just parroting method once demonstrated
by Michel Walsh

it works for "casting" to other types as well


-- to end up with date/time field, all nulls
IIf(True,Null,#1/1/1900#)

-- works for text
IIf(True,Null," ") <--get type text(255), all null

-- or use Cxxx functions
IIf(True,Null,CCur(0)) <--get type Currency, all null
IIf(True,Null,CDbl(0)) <--get type Double, all null
 
Back
Top