If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using like in an iif statement
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. |
#2
|
|||
|
|||
Using like in an iif statement
On Thu, 12 Apr 2007 18:28:00 -0700, Matt Robson
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|