Query Return Yes or No

[kagoodwin13]

If I have a query that returns a result, can I layer another query on top that will return "Yes" or "No" based on if the result is null or not? I haven't been able to figure out the logic statement yet.

My query cross-references an ID number against 4 tables that all collect a piece of information about the ID number. The ID number is a part number, and the tables are document numbers with the file path for the document concerning the part number, so I want a query to return whether documents 1-4 have been submitted per part number. Right now I have the query returning the file path, which is good. I am wondering if I can layer another query over top that says If Not Null, return "Yes", Else return "No."

Right now query returns:

Part Number
Document 1
Document 2
Document 3
Document 4

1A
C:\1A-D1.xlsx


C:\1A-D4.xlsx

2B

C:\2B-D2.pdf



3C


C:\3C-D3.txt
C:\3C-D4.xlsx

I would like it to return instead:

Part Number
Document 1
Document 2
Document 3
Document 4

1A
Yes
No
No
Yes

2B
No
Yes
No
No

3C
No
No
Yes
Yes

Thanks for any help you can provide. Let me know if you need more clarification.

[Dal Jeanis]

You could use the NZ function to translate NULL to another value. Something like NZ( ReturnFileName > "", False).

If the ReturnFileName is NULL, then comparing it to the empty string returns NULL, and NZ changes that NULL to False. If ReturnFileName is not NULL, then it's greater than the empty string, so you get True.

[kagoodwin13]

Thanks for the tip.

Document 1: IIf(Nz([Doc1]>"")=-1,"Yes","No") is the solution.