David-
You're close, but no cigar.
When chkLessThan is true, this becomes:
Where ([Comparator] <> "<") = True
and when chkLessThan is false this becomes:
Where True = True
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
On Aug 1, 2017, at 22:12, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John, you're a TexMex border genius. We should have built the wall to keep you in.
this works and I have no idea how this works. I put the following new field into my query:
FilterLessThan: IIf([Forms]![frmSelectSample]![chkLessThan],[Comparator]<>"<",True)
and then in the criteria just as you said, I put TRUE
I cannot see how this can work, but it does. No surprise to you I am sure.
In Datasheet view, the new field, FILTERLESSTHAN, always shows to be TRUE (-1) for every row, no matter the value in the COMPARATOR field. How can it filter anything when the value is always True and the criteria is set to True?
When I view the SQL that is generated I think maybe I can follow it. this is the WHERE clause generated:
WHERE (((IIf([Forms]![frmSelectSample]![chkLessThan],[Comparator]<>"<",True))=True)
When chkLessThan is true, does this become:
Where [Comparator] <> "<"
and when chkLessThan is false does this become:
Where True = True
How in the world did you ever think of this??? This is depressing for a mere mortal.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
You cannot dynamically replace a comparison operator, so trying to substitute ">" or "=" or "<" into a predicate just won't work.
The expression I gave you must stand by itself. In the query grid, put the IIf expression as a field, leave Show checked for now to see what it's doing, and put TRUE in the Criteria line underneath it.
John Viescas, author
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
On Aug 1, 2017, at 12:47, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I hope you have a bundle of patience this morning as I still am not comprehending this.
I am not trying to return a comparison operator; at least not intentionally. I am trying to return the "less than" symbol as a simple text string. In my table the field COMPARATOR is a text field and the values in the field can only be one of three single character text strings. The options are =, > or <. All are just strings, not actually a comparison operator. I am just using them as short hand for EQUAL TO, LESS THAN, and GREATER THAN.
I want the IIF statement to return either the "less than" symbol to use as the filter (and actually filter for NOT Less Than), or return "Like *" so that nothing is filtered.
I used your recent suggestion and all results are all filtered out, no matter what the state of chkLessThan is. That is, the query returns no results. I also tried replacing your final TRUE with Like "*" and still get an empty list box.
Remember, this IIF statement is in the criteria row of my query, under the field COMPARATOR.
If chkLessThan is true, I just want the IIF to return the text <> "<" into the criteria row. I can hard code <> "<" into the criteria row and it works just fine (removes all results where COMPARATOR text field is equal to "<").
If chkLessThan is false, I want the IIF to return Like "*" into the criteria row. I can hard code Like "*" into the criteria row and it works just fine.
Perhaps I am trying to do something with the IIF that cannot be done.
I have to leave and will not be back for several hours to reply to any questions. Thank you for your help.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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
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
On Aug 1, 2017, at 09:33, david.pratt@... [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?
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
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 -----Sent: Monday, July 31, 2017 3:16 PMSubject: [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 toIIf([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_tblAnalysesINNER 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 (22) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar