Ignore critera if Null 2

[onefootout]

Hi All,

I'm just getting back into sql/access after a 5 year hiatus. I feel like I should be able to solve this problem by myself, but I'm stymied.

I have a query being run by values entered in a form, like so:

SELECT Txns.InvoiceNumber, Txns.SkillCode, Txns.EmpName
FROM Txns
WHERE (((Txns.InvoiceNumber)=[forms]![_skillcodechange]![invnum]) AND ((Txns.EmpName)=[forms]![_skillcodechange]![empname]));

I need it to do something like this:

SELECT Txns.InvoiceNumber, Txns.SkillCode, Txns.EmpName
FROM Txns

WHERE

-->these two fields return values

(((Txns.InvoiceNumber)=[forms]![_skillcodechange]![invnum]) AND
((Txns.EmpName)=[forms]![_skillcodechange]![empname]));

-->or if there's nothing in [empname], ignore that input and just query by the value in [invnum]

I thought about using the Nz() function, but I'm not sure if that's the right solution. I don't want to return a value if there's no match, I want to ignore it.

Thanks so much!

 

[dhookom]

Try something like:

SELECT Txns.InvoiceNumber, Txns.SkillCode, Txns.EmpName
FROM Txns
WHERE (Txns.InvoiceNumber=[forms]![_skillcodechange]![invnum] OR [forms]![_skillcodechange]![invnum] IS Null) AND (Txns.EmpName=[forms]![_skillcodechange]![empname] OR [forms]![_skillcodechange]![empname] is Null);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

SELECT Txns.InvoiceNumber, Txns.SkillCode, Txns.EmpName
FROM Txns
WHERE (Txns.InvoiceNumber=[forms]![_skillcodechange]![invnum] OR [forms]![_skillcodechange]![invnum] Is Null)
AND (Txns.EmpName=[forms]![_skillcodechange]![empname] OR [forms]![_skillcodechange]![empname] Is Null);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[onefootout]

dhookom & PHV,

Works like a charm. Thanks a lot.