Building a combo list without nulls

[Karen]

I'll be building a SQL statement to use as the rowsource for a combobox.

To populate the list I'll be pulling various names stored for a particular
report. For example, the report will have the names of Person1, Person2,
Person3 and Person4 so I want to populate the combo with those names. I'm
imagining something like:

cboRelatedNames.RowSource = SELECT Person1 from InfoTable _
UNION SELECT Person2 from InfoTable _
UNION SELECT Person3 from InfoTable _
UNION SELECT Person4 from InfoTable _
WHERE InfoTable.fkReportID = 4

OK, that should work but what if Person 3 is unknown at this time, I don't
want a list with two names, a blank and then another name. How do I do a
Union Select but drop the null items?

 

[John Vinson]

OK, that should work but what if Person 3 is unknown at this time, I don't
want a list with two names, a blank and then another name. How do I do a
Union Select but drop the null items?

By using an IS NOT NULL clause in each SELECT. You also need to set
the RowSource to a STRING; SQL is one language and VBA is another. And
you also need to include the fkReportID criterion in EACH select,
otherwise you'll get the fkReportID = 4 for Person4 and all records
for the other three people:

cboRelatedNames.RowSource = "SELECT Person1 from InfoTable" _
& " WHERE InfoTable.fkReportID = 4 AND Person1 IS NOT NULL" _
& " UNION SELECT Person2 from InfoTable" _
& " WHERE InfoTable.fkReportID = 4 AND Person2 IS NOT NULL" _
& " UNION SELECT Person3 from InfoTable " _
& " WHERE InfoTable.fkReportID = 4 AND Person3 IS NOT NULL" _
& " UNION SELECT Person4 from InfoTable" _
& " WHERE InfoTable.fkReportID = 4 AND Person4 IS NOT NULL;"

I'd really suggest normalizing your Person field into another table!!

 

[Graham R Seach]

Karen,

cboRelatedNames.RowSource = SELECT Person1 from InfoTable WHERE Person1 IS
NOT NULL _
UNION SELECT Person2 from InfoTable WHERE Person2 IS NOT NULL _
UNION SELECT Person3 from InfoTable WHERE Person3 IS NOT NULL _
UNION SELECT Person4 from InfoTable WHERE Person4 IS NOT NULL _
WHERE InfoTable.fkReportID = 4

.... or, depending on your version of Access ...

cboRelatedNames.RowSource = SELECT X.* FROM
(SELECT Person1 As Person from InfoTable WHERE Person1
UNION SELECT Person2 As Person from InfoTable
UNION SELECT Person3 As Person from InfoTable
UNION SELECT Person4 As Person from InfoTable
WHERE InfoTable.fkReportID = 4) As X
WHERE X.Person IS NOT NULL


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Karen]

John,

Actually, I knew it was a string, just sloppy with the description.

I would like to normalize to a separate table but the customer wants to
capture information at that time so if information related to people over
time, any reprints of the report would still have the original values. I've
thought about using timestamps everywhere but that seems pretty convoluted
also.

Thanks very much for the 'Is not null' and the reminder of the 'where'
clause.

 

[Karen]

Graham,

Thanks very much. I'm using Access 2003 for this project so first one will
do just fine.

--
Karen

Karen,

cboRelatedNames.RowSource = SELECT Person1 from InfoTable WHERE Person1 IS
NOT NULL _
UNION SELECT Person2 from InfoTable WHERE Person2 IS NOT NULL _
UNION SELECT Person3 from InfoTable WHERE Person3 IS NOT NULL _
UNION SELECT Person4 from InfoTable WHERE Person4 IS NOT NULL _
WHERE InfoTable.fkReportID = 4

... or, depending on your version of Access ...

cboRelatedNames.RowSource = SELECT X.* FROM
(SELECT Person1 As Person from InfoTable WHERE Person1
UNION SELECT Person2 As Person from InfoTable
UNION SELECT Person3 As Person from InfoTable
UNION SELECT Person4 As Person from InfoTable
WHERE InfoTable.fkReportID = 4) As X
WHERE X.Person IS NOT NULL


Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia