No records Returned

  • Thread starter Thread starter Nick
  • Start date Start date
N

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
 
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)
 
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)
John Viescas said:
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)
Nick said:
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
Click to expand...
Click to expand...
 
Back
Top