Returning unique results only is control is Null

[pi3832]

I'm clueless.

Well, that's probably true just in general, but at the moment I'm
clueless about an Access query and maybe someone out there can help.

I have a table ([Components]) with two fields, [Lot number] and
[Component Code]. Neither field is unique in that table. A given
[Component Code] can be in records with many [Lot number]s, and vise
versa.

I want to query based on a [Component Code] to get a [Lot Number], and
then turn it around and get *all* of the records with that [Lot
Number], including ones where the [Component Code] doesn't match the
original query.

I've got a query that will do all that, as long as the search control
isn't Null. I need the query to return all the [Lot Numbers] if the
control is Null, but I'm getting a "too complex..." error.

I'm guessing this is because as I have the query now, the number of
records is multiplying ferociously when I do a Null search. E.g., if
there are 5 [Lot Number]s with 5 [Component Code]s each (25 records),
*each* record is returning 5 queried records (125 records!) when I turn
around to get all of the records associated with that [Lot Number].

I need to just get unique [Lot Number]s, but *only* when the control
([Forms]![Search]![SearchComp1]) is Null. (Actually, maybe I can
always get unique [Components]![Lot Numbers]... though, I don't know
how to do that, either.)

Any ideas?

Current SQL:
SELECT DISTINCTROW Components_1.[Lot Number], Components_1.[Component
Code]
FROM Components INNER JOIN Components AS Components_1 ON
Components.[Lot Number] = Components_1.[Lot Number]
WHERE (((Components.[Component
Code])=Val([Forms]![Search]![SearchComp1]))) OR
(((IsNull([Forms]![Search]![SearchComp1]))=True));

 

[pi3832]

I've fiddled some more. I created a simpler query, that just returns
the [Lot Number].

It still gives me an error when the control is Null. Maybe I'm not
getting the syntax correct?

Simpler SQL:

SELECT DISTINCT Components.[Lot Number]
FROM Components
GROUP BY Components.[Component Code],
IsNull([Forms]![Search]![SearchComp1]), Components.[Lot Number]
HAVING (((Components.[Component
Code])=Val([Forms]![Search]![SearchComp1]))) OR
(((IsNull([Forms]![Search]![SearchComp1]))=True));

 

[pi3832]

Never mind. I figured it out.

The function Val() was belching on the Null value. I changed it to

Val( " " &[Forms]![Search]![SearchComp1])

and it was happy. (Now, when the control is Null, Val() gets " ",
instead of Null.)

Of course, now I'm back to the duplicates problem, but that should be
solvable now that I can get the query to run.