Re: [MS_AccessPros] IIF as Query Criteria

 

David-

You cannot return a comparison operator - you have to do the comparison within the IIf.

IIf([Forms]![<name of your form>]![chkLessThan], [Comparator] <> "<", True)

If chkLessThan is set, then return the rows where the Comparator is not "<", otherwise return all rows.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Aug 1, 2017, at 09:33, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John but I am not following this.

I am wanting to evaluate chkLessThan in the criteria of the COMPARATOR field.  If chkLessThan is true, then I want the criteria for the COMPARATOR field, which is a string field, to be set so as to filter out any results where the COMPARATOR field shows the less than symbol.  If chkLessThan is false, I don't want to filter out any of the results.

Thus if chkLessThan is True, I don't want to evaluate anything.  I just want to return a filter criteria that says "any result where the COMPARATOR field is not equal to the less than symbol";   or <> "<".

And then if chkLessThan is false, I want to return a filter criteria that does not filter any results; Like "*".

Will the IIF statement not return a different string depending upon the result of the evaluation of chkLessThan?  Am I just completely misusing the IIF statement?

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

First part: The test you want to perform for true / false.  In this case, it's [Forms]![name of your form]![chkLessThan]

Second part: The expression you want evaluated if the first part is True - when ChkLessThan is selected. <> "<" is not a valid expression.  Maybe you want [Comparator] <> "<", or MAYBE you want another embedded IIf: IIf([Comparator] <> "<", [test to perform if comparator is not <], [test to perform if comparator is <])

Third part: The expression you want evaluated if ChkLessThan isn't selected.

Does that help?

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Jul 31, 2017, at 23:30, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

OK, I am trying to follow.  Here is what I think I am doing.  So three parts: (1) criteria, (2) result if true, (3) result if false; all three parts separated by commas.  And the IIF statement is located in the query Criteria row for the field named COMPARATOR.

STATEMENT: IIF(Me.chkLessThan, <> "<", "") 

      located in the query criteria row under the field named COMPARATOR

Part 1, the Criteria:

         IIf chkLessThan (meaning if chkLessThan is = true)

Part 2, Result if True:  <> "<"

  means set the criteria for the COMPARATOR field such that the query only shows the rows of data where the string character in the COMPARATOR field is not the 'less than' symbol.  Perhaps I should enclose in quotes differently?  Maybe "<> '<' " ?

Part 3, Result if False: ""

 means if chkLessThan is false, then the criteria for the COMPARATOR field should be set to "" (blank) so that no filtering occurs.  Perhaps this should be a wild card instead like "*"?

Until I can get the form to quit closing on me, I can't try any of these.

Thank you for the response,

---In MS_Access_Professionals@yahoogroups.com, <davewillgmale@...> wrote :



I am not an expert, but your Iif query seems wrong. An Iif query has three parts separated by commas: Criteria, result if True, result if False. In your query, the first part could be True or False as it's a checkbox, but the second part is not a result - you've put <>"<", not just "<". Your third part is OK and gives a blank if the checkbox is False.

 

----- Original Message -----

From: david.pratt@... [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Monday, July 31, 2017 3:16 PM

Subject: [MS_AccessPros] IIF as Query Criteria

 

I have an unbound form whose purpose is to show the analytical results from various water samples. The form contains two list boxes.  The first list box contains the list of samples and the second list box shows the results of the selected sample. I also have two combo boxes which are used to filter the list of samples. 

All of this works well. However, I am trying now to be able to also filter the sample results such that if any of the sample results are "less than" the detection limit, the result is not included in the list of sample results.  I have a field named COMPARATOR in the tblSampleResults and that field is in the query used by the list box.  

I added a checkbox to the form as chkLessThan and want to use the value in that checkbox to determine if any "less than" results show up in the list box.  So far I cannot get this to work.  I can "hard code" the query criteria by placing <> "<" in the criteria for COMPARATOR and that works fine.  But I cannot get the syntax correct for an IIF statement in the criteria.

Basically I need the query criteria for the COMPARATOR field to be:

       IIf([me].[chkLessThan],<>"<","")

When I put this in the query criteria Access changes it to

     IIf([me].[chkLessThan],([tblSampleResults].[Comparator])<>"<","")

When I go to form view, Access thinks this is a Parameter query and asks me what the value is for Me.chkLessThan.

Can you help me with the correct syntax or is perhaps IIF not allowed in the criteria of a SQL statement?

The SQL which is generated by the query design is:

SELECT [ParameterName] & ", " & [Units] AS [Parameters], [Result]) AS Results, tblSampleResults.Comparator
FROM lu_tblAnalyses

INNER JOIN ((tblSampleResults INNER JOIN tblParameters ON tblSampleResults.ParameterID = tblParameters.ParameterID) INNER JOIN lu_tblParameterGroups ON tblParameters.ParameterGroupID = lu_tblParameterGroups.ParameterGroupID) ON lu_tblAnalyses.AnalysisID = tblParameters.AnalysisID

WHERE (((tblSampleResults.Comparator)=IIf([me].[chkLessThan],(tblSampleResults.Comparator)<>"<","")) AND ((tblSampleResults.SampleID)=[Forms]![frmSelectSample]![lstSamples]))
ORDER BY lu_tblParameterGroups.ParameterGroupSort, tblParameters.ParameterSort;

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (18)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___