I am trying to query a couple of fields to base the final output. Here is what I have so far, but it is just not working...can someone please help me.

IIf(IsNull([Num_Files] And [Failed_YN]="N","0",IIf(IsNull([Num_Files] And [Failed_YN]="Y",".",[Num_Files]))))


So, I have a field called "Num_Files" in a table, and "Failed_YN" in another table and the two tables are linked by a key field. If the unit did not fail (N), and the Num_Files is null, then I want it to put a zero. If the unit did fail (Y), and the Num_Files is null, then I want it to put a period. Else, keep the value in the Num_Files field.
THANKS SO MUCH!

For starters, you need to close off the IsNull function:

IIf(IsNull([Num_Files]) And [Failed_YN]="N",...

Is Failed_YN a text field? That's how you're treating it. If it's a Yes/No field:

And [Failed_YN]=False

or

And [Failed_YN]=0

__________________
Paul
MS Access MVP
www.BaldyWeb.com

Thank you so much for your reply Paul. That is exactly what I was missing...")" and it is working.

I was curious...is there a difference or preferance in doing it this way instead...instread of using the IsNull Function?

IIf([Num_Files] is null And [Failed_YN]="N"

Happy to help. I'm not sure there would be any performance difference either way. I'd use whichever one you're more comfortable with.

Welcome to the site by the way!

__________________
Paul
MS Access MVP
www.BaldyWeb.com

I am so thankful for people like you...who help people like me! I am greatful for resources like this that help me get my work done! I am sure I will be back over and over again!

Just a suggestion, I'd go with:

IIf(IsNull([Num_Files]), IIf([Failed_YN]="Y", ".", "0"), [Num_Files])

In the original format, if [Num_Files] was null and [Failed_YN] happened to be anything other than "Y" or "N", the formula would have bombed when it tried to apply the (null) [Num_Files]. This way, if [Num_Files] is null, you return "." if [Failed_YN] is "Y" and "0" otherwise.