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 wildcards in iif in expression builder
I'm stuck in creating a selection criteria for a query.
Based on a value in a Form, I need to select all records which are "Yes", if this text in the form is "SMD" or, if this value is not "SMD", I want all records (both "Yes" and "No"). The first part of the iif statement is no problem and works fine, but how can I select all records (what do I need in the falsepart of the iif)? It looks like I need to use a wildcard here to select all records but I can't make it work. IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; ) |
#2
|
|||
|
|||
Using wildcards in iif in expression builder
Switch the Query to SQL View (View menu.)
Locate the WHERE clause. Change it to look like this: WHERE IIf([Forms]![Frm_DATA Extract]![Area]="SMD", True, [YourYesNoField]) This works because the WHERE clause is something that evaluates to True or False (or Null) for each record. The expression above evaluates to TRUE for every record when the Area text box contains SMD. If the text box doesn't contain that, the WHERE clause evaluates to True only if the text box is true (checked.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "filip" wrote in message ... I'm stuck in creating a selection criteria for a query. Based on a value in a Form, I need to select all records which are "Yes", if this text in the form is "SMD" or, if this value is not "SMD", I want all records (both "Yes" and "No"). The first part of the iif statement is no problem and works fine, but how can I select all records (what do I need in the falsepart of the iif)? It looks like I need to use a wildcard here to select all records but I can't make it work. IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; ) |
#3
|
|||
|
|||
Using wildcards in iif in expression builder
"Allen Browne" wrote: Switch the Query to SQL View (View menu.) Locate the WHERE clause. Change it to look like this: WHERE IIf([Forms]![Frm_DATA Extract]![Area]="SMD", True, [YourYesNoField]) This works because the WHERE clause is something that evaluates to True or False (or Null) for each record. The expression above evaluates to TRUE for every record when the Area text box contains SMD. If the text box doesn't contain that, the WHERE clause evaluates to True only if the text box is true (checked.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "filip" wrote in message ... I'm stuck in creating a selection criteria for a query. Based on a value in a Form, I need to select all records which are "Yes", if this text in the form is "SMD" or, if this value is not "SMD", I want all records (both "Yes" and "No"). The first part of the iif statement is no problem and works fine, but how can I select all records (what do I need in the falsepart of the iif)? It looks like I need to use a wildcard here to select all records but I can't make it work. IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; ) Allen, Thanks for your comments, but I still didn't make it work. This selection criteria is only one of the many criterias in this selection query. Underneath you can find the full SQL view. Everything is working fine except this above mentioned problem. SELECT Tbl_DCAFS_Data.Date, Tbl_DCAFS_Data.NoTec, Tbl_DCAFS_Data.Model, Tbl_DCAFS_Data.Comp, Tbl_DCAFS_Data.PartNo, Tbl_DCAFS_Data.Defect, Tbl_lookup_ProfitCtr.[Profit Ctr], Tbl_lookup_ProfitCtr.Area, IIf(Tbl_Assemblies!SMD=Yes And Tbl_Problem_Classificatie!SMD=Yes,"Yes","No") AS SMD, IIf([Station]="Insercion-plant160","Yes","No") AS Visual FROM Tbl_Problem_Classificatie INNER JOIN ((Tbl_lookup_ProfitCtr INNER JOIN Tbl_Assemblies ON Tbl_lookup_ProfitCtr.[Profit Ctr] = Tbl_Assemblies.[Profit Ctr]) INNER JOIN Tbl_DCAFS_Data ON Tbl_Assemblies.DCAFS_Model_Name = Tbl_DCAFS_Data.Model) ON Tbl_Problem_Classificatie.Defect = Tbl_DCAFS_Data.Defect WHERE (((Tbl_DCAFS_Data.Date)=[Forms]![Frm_DATA Extract]![Start_Date] And (Tbl_DCAFS_Data.Date)=[Forms]![Frm_DATA Extract]![Stop_Date]) AND ((Tbl_DCAFS_Data.Model)=IIf([Forms]![Frm_DATA Extract]![Model] Is Not Null,[Forms]![Frm_DATA Extract]![Model],[Tbl_DCAFS_Data]![Model])) AND ((Tbl_DCAFS_Data.PartNo)=IIf([Forms]![Frm_DATA Extract]![Part] Is Not Null,[Forms]![Frm_DATA Extract]![Part],[Tbl_DCAFS_Data]![PartNo])) AND ((Tbl_DCAFS_Data.Defect)=IIf([Forms]![Frm_DATA Extract]![Defect] Is Not Null,[Forms]![Frm_DATA Extract]![Defect],[Tbl_DCAFS_Data]![Defect])) AND ((Tbl_lookup_ProfitCtr.Area)=IIf(([Forms]![Frm_DATA Extract]![Area]"ALL") And ([Forms]![Frm_DATA Extract]![Area]"TSUB") And ([Forms]![Frm_DATA Extract]![Area]"SMD"),[Forms]![Frm_DATA Extract]![Area],[Area])) AND ((IIf([Tbl_Assemblies]![SMD]=Yes And [Tbl_Problem_Classificatie]![SMD]=Yes,"Yes","No"))=IIf([Forms]![Frm_DATA Extract]![Area]="SMD","Yes")) AND ((Tbl_DCAFS_Data.AreaAffected)=IIf([Forms]![Frm_DATA Extract]![Area]="TSUB","Manual/Final-Plant160-TSUB",[Tbl_DCAFS_Data]![AreaAffected]))) ORDER BY Tbl_DCAFS_Data.Date; |
#4
|
|||
|
|||
Using wildcards in iif in expression builder
As you found, that WHERE clause is not going to work.
If the Date field is null, it won't match anything. Therefore the record will be rejected regardless of whether the text boxes on the form have anything in them or not. (Or perhaps [Date] is a required field, in a simple query where the field cannot be null?") Similarly, this phrase: AND ((Tbl_DCAFS_Data.Model)= IIf([Forms]![Frm_DATA Extract]![Model] Is Not Null, [Forms]![Frm_DATA Extract]![Model], [Tbl_DCAFS_Data]![Model])) compares the Model field to either: a) the Model box on the form if that's not null, or else b) itself. Again, if the field itself is null, then comparing it to itself will not match, since Null doesn't match Null. If that's news, see the first example in: Common Errors with Null at: http://allenbrowne.com/casu-12.html To correct that use: AND IIf([Forms]![Frm_DATA Extract]![Model] Is Null, TRUE, [Tbl_DCAFS_Data]![Model] = [Forms]![Frm_DATA Extract]![Model]) The same problems will occur with the other phrases in the WHERE clause. While it is possible to craft a convoluted WHERE clause such that the IIf() do evaluate to TRUE when the text boxes are null, it gets unweildy, not to mention inefficient to execute. An alternative approach is to build the WHERE clause from only the boxes on the form where the user actually entered something. You can then use this WHERE clause as the Filter for a form, the WhereConditon for OpenReport, or even to dynamically assign the SQL of the QueryDef if you need to. If you are interested in that approach, download the sample database from this article: Search form - Handle many optional criteria at: http://allenbrowne.com/ser-62.html It may take half an hour or so to digest this, but it will be time well spent, as it's so flexible. Oh, and BTW, a field named Date is not a good idea: Access will misinterpret it as the system date in some contexts, and spit the dummy in other contexts (queries.) Here's a list to refer to and avoid when designing tables: http://allenbrowne.com/Ap****ueBadWord.html Hope the dynamically generated filter opens new (and simpler) possibilties for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "filip" wrote in message ... "Allen Browne" wrote: Switch the Query to SQL View (View menu.) Locate the WHERE clause. Change it to look like this: WHERE IIf([Forms]![Frm_DATA Extract]![Area]="SMD", True, [YourYesNoField]) This works because the WHERE clause is something that evaluates to True or False (or Null) for each record. The expression above evaluates to TRUE for every record when the Area text box contains SMD. If the text box doesn't contain that, the WHERE clause evaluates to True only if the text box is true (checked.) "filip" wrote in message ... I'm stuck in creating a selection criteria for a query. Based on a value in a Form, I need to select all records which are "Yes", if this text in the form is "SMD" or, if this value is not "SMD", I want all records (both "Yes" and "No"). The first part of the iif statement is no problem and works fine, but how can I select all records (what do I need in the falsepart of the iif)? It looks like I need to use a wildcard here to select all records but I can't make it work. IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; ) Allen, Thanks for your comments, but I still didn't make it work. This selection criteria is only one of the many criterias in this selection query. Underneath you can find the full SQL view. Everything is working fine except this above mentioned problem. SELECT Tbl_DCAFS_Data.Date, Tbl_DCAFS_Data.NoTec, Tbl_DCAFS_Data.Model, Tbl_DCAFS_Data.Comp, Tbl_DCAFS_Data.PartNo, Tbl_DCAFS_Data.Defect, Tbl_lookup_ProfitCtr.[Profit Ctr], Tbl_lookup_ProfitCtr.Area, IIf(Tbl_Assemblies!SMD=Yes And Tbl_Problem_Classificatie!SMD=Yes,"Yes","No") AS SMD, IIf([Station]="Insercion-plant160","Yes","No") AS Visual FROM Tbl_Problem_Classificatie INNER JOIN ((Tbl_lookup_ProfitCtr INNER JOIN Tbl_Assemblies ON Tbl_lookup_ProfitCtr.[Profit Ctr] = Tbl_Assemblies.[Profit Ctr]) INNER JOIN Tbl_DCAFS_Data ON Tbl_Assemblies.DCAFS_Model_Name = Tbl_DCAFS_Data.Model) ON Tbl_Problem_Classificatie.Defect = Tbl_DCAFS_Data.Defect WHERE (((Tbl_DCAFS_Data.Date)=[Forms]![Frm_DATA Extract]![Start_Date] And (Tbl_DCAFS_Data.Date)=[Forms]![Frm_DATA Extract]![Stop_Date]) AND ((Tbl_DCAFS_Data.Model)=IIf([Forms]![Frm_DATA Extract]![Model] Is Not Null,[Forms]![Frm_DATA Extract]![Model],[Tbl_DCAFS_Data]![Model])) AND ((Tbl_DCAFS_Data.PartNo)=IIf([Forms]![Frm_DATA Extract]![Part] Is Not Null,[Forms]![Frm_DATA Extract]![Part],[Tbl_DCAFS_Data]![PartNo])) AND ((Tbl_DCAFS_Data.Defect)=IIf([Forms]![Frm_DATA Extract]![Defect] Is Not Null,[Forms]![Frm_DATA Extract]![Defect],[Tbl_DCAFS_Data]![Defect])) AND ((Tbl_lookup_ProfitCtr.Area)=IIf(([Forms]![Frm_DATA Extract]![Area]"ALL") And ([Forms]![Frm_DATA Extract]![Area]"TSUB") And ([Forms]![Frm_DATA Extract]![Area]"SMD"),[Forms]![Frm_DATA Extract]![Area],[Area])) AND ((IIf([Tbl_Assemblies]![SMD]=Yes And [Tbl_Problem_Classificatie]![SMD]=Yes,"Yes","No"))=IIf([Forms]![Frm_DATA Extract]![Area]="SMD","Yes")) AND ((Tbl_DCAFS_Data.AreaAffected)=IIf([Forms]![Frm_DATA Extract]![Area]="TSUB","Manual/Final-Plant160-TSUB",[Tbl_DCAFS_Data]![AreaAffected]))) ORDER BY Tbl_DCAFS_Data.Date; |
Thread Tools | |
Display Modes | |
|
|