No records Returned

[Nick]

I am using this "In (SELECT [Work Center] FROM
[qytbWorkCentreList-Combine])" in a query and it works
when there is an entry in the sub query.
How can I add the above so that if [qytbWorkCentreList-
Combine] has no value, that is null value, all records are
returned.

I hope this makes sense.

Regards
Nick

 

[John Viescas]

WHERE MyField In (SELECT [Work Center] FROM
[qytbWorkCentreList-Combine])
OR ((SELECT [Work Center] FROM
[qytbWorkCentreList-Combine]) IS NULL)

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[John Viescas]

Sorry. That will blow up if the subquery returns more than one row. If
you're using Access JET, you can try this, but it might be slow:

WHERE MyField In (SELECT [Work Center] FROM
[qytbWorkCentreList-Combine])
OR (DLookup("[Work Center]",
"[qytbWorkCentreList-Combine]") IS NULL)


--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

WHERE MyField In (SELECT [Work Center] FROM
[qytbWorkCentreList-Combine])
OR ((SELECT [Work Center] FROM
[qytbWorkCentreList-Combine]) IS NULL)

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I am using this "In (SELECT [Work Center] FROM
[qytbWorkCentreList-Combine])" in a query and it works
when there is an entry in the sub query.
How can I add the above so that if [qytbWorkCentreList-
Combine] has no value, that is null value, all records are
returned.

I hope this makes sense.

Regards
Nick