IIF statement for attachments

[aamer]

I am having problem setting up a querry with IIF statement.



I have made a querry which shows if there are attachments in table.
if there is an attachment it shows (something like) @(1) and if there is no attachment it shows @(0) in querry filed.

what i am trying to do is, if there is an attachment it should say "YES" and If there is no attachment it should say "NO"

for which I have tried the following formulas in querry but its none are working.

TEST: IIf([purattach] Is Null,"YES","NO")
TEST: IIf([purattach.FileData] Is Null,"YES","NO")
TEST: IIf([purattach.FileName] Is Null,"YES","NO")
TEST: IIf([purattach.FileType] Is Null,"YES","NO")


Thanks

[June7]

What do you mean by not working - error message, wrong results, no results?
By your narrative of what you want, you have the "Yes" and "No" backwards.

[aamer]

afteryour reply i tested the following to get the error message.

TEST: IIf([purattach] Is Null,"NO","YES")


ERROR MESSAGE:

The expression you entered has an invalid . (dot) or ! operator or invalid parentheses.

You may have enteredan invalid identifier or typed parentheses following the null constant

[June7]

I tested Is Null and it failed but this worked:
IIf(IsNull([purattach]), "No", "Yes")

[aamer]

I tried the following in querry
Expr1: IIf(IsNull([purattach]),"No","Yes")
and am getting the following error message.

The multi-valued field '[purattach]' is not valid in the expression 'Expr1: IIf(IsNull([purattach]),"No","Yes")'.

[June7]

Crud! Multi-value fields, don't like 'em, never use 'em.

I should have guessed from the name this would be an Attachment datatype. I've never used this field type.

If this doesn't work, I have nothing else:
IIf([purattach]="", "No","Yes")

[ketbdnetbp]

Like, June7, I do not use attachment fields. Therefore, this is just an idea. However, since all else has failed, at this point, you may want to try:

IIF(Len([purattach])>0,"Yes","No")

All the best,

Jim

[boblarson]

For multi-valued fields:

Expr1: IIf(IsNull([purattach].Value),"No","Yes")

[boblarson]

In fact, this is a good reference for those:
http://office.microsoft.com/en-us/ac...33722.aspx#BM7

[aamer]

none are working for me

[June7]

Read this article http://www.techrepublic.com/blog/mso...-20072010/5134

SELECT ID, purattach.Value, IIf(IsNull([purattch].[Value]),"No","Yes") AS Condition FROM Table1;

SELECT ID, purattach.Value FROM Table1 WHERE ((Not (purattach.Value) Is Null));

This works for text datatype where data has been entered by multi-value enabled combobox or listbox.

For attachment datatype use FileData or FileName or FileType instead of Value.

[aamer]

thank you june for pointing me in the right direction.
thank u boblarson for your IIF statement i used but with the modification june poined out.

I used the following and it worked like a charm
Expr1: IIf(IsNull([purattach].[FileData]),"No","Yes")

[boblarson]

For attachment datatype use FileData or FileName or FileType instead of Value.

I missed that it was an attachment (even though it was clearly stated in the title).

[aamer]

thats ok, still with your help i was able to fix it