Using like in an iif statement

[Matt Robson]

I am using a form to supply data to a query it has two different IIF
statements embedded the first returns an empty string if a field is left
blank. the second checks a different box on the form to determine whether the
user wants to search for an exact value or a similiar value.

IIf([Forms]![Purchasing list]![Part
Number]="isnull","",IIf([forms]![purchasing list]![part number1]=Yes, Like
[Forms]![Purchasing list]![Part Number] & "*",[forms]![purchasing list]![PArt
number]))

Access changes it to:

IIf([Forms]![Purchasing list]![Part
Number]="isnull","",IIf([forms]![purchasing list]![part number1]=Yes,([A
List].[PART NO]) Like [Forms]![Purchasing list]![Part Number] &
"*",[forms]![purchasing list]![PArt number]))

-Adding the ([A List].[PART NO])
I have changed the statement and used it to add -99 to the Part Number
instead of using the like operator &"*" and access does not add the extra
text and the statement works as intended. The problem seems to be that when
I use the "like" operator I am doing something wrong.

 

[John W. Vinson]

I am using a form to supply data to a query it has two different IIF
statements embedded the first returns an empty string if a field is left
blank. the second checks a different box on the form to determine whether the
user wants to search for an exact value or a similiar value.

IIf([Forms]![Purchasing list]![Part
Number]="isnull","",IIf([forms]![purchasing list]![part number1]=Yes, Like
[Forms]![Purchasing list]![Part Number] & "*",[forms]![purchasing list]![PArt
number]))


Well, this is not checking to see if the Part Number is null. It is checking
to see if the textbox contains the literal text string "isnull". The second
test will fail as well, since you cannot pass operators such as LIKE out from
an IIF statement - only actual values.

I'd suggest an altogether different criterion:

WHERE [Forms]![Purchasing List]![Part Number] Is Null
OR ([Forms]![Purchasing List]![Part number1] = True AND [Part Number] LIKE
[Forms]![Purchasing List]![PartNumber] & "*")
OR ([Forms]![Purchasing List]![Part number1] = False AND [Part Number]
=[Forms]![Purchasing List]![PartNumber])

John W. Vinson [MVP]