John, the "comparator" is not comparing against anything, it is merely a string. The COMPARATOR will always be either "=", "<", or ">". All of the analytical procedures have lower detection limits. For instance a nitrate analysis may have a lower detection limit of 1.0 PPM. so if the concentration of nitrate is below 1.0 PPM, we don't know what the actual result is, we just know it is less than 1.0. So the result is reported as Nitrate <1.0 PPM. If the water sample is very pure, a lot of the analyses are reported as "< x", and I am just trying to filter out those results so that only the measurable contaminants are shown in the list box.
To accomplish my goal, I added an unbound control, chkLessThan, that I am trying to use as query criteria to prevent all the "less than" results from showing up in the query results that populate the list box. If chkLessThan is True, I want to filter the results. And if chkLessThan is false I do not want the results filtered.
Based on your syntax below I am now trying:
IIf([Forms]![frmSelectSample]![chkLessThan]=True,([tblSampleResults].[Comparator]) <>"<", ([tblSampleResults].[Comparator]) Like "*")
Now I have somehow developed a new problem which seems like possibly an Access but. Now when I go from Design view to Form View, the form just closes. I can re-open it in Design View, but I am unable to open it in Form view. It just closes the form each time I try to go to Form view.
Any ideas on both problems?
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
It's not clear what you're comparing tblSampleResults.Comparator to. Result?
I think you need something like:
IIf([Forms]![name of your form]![chkLessThan], [Result] < [Comparator], [Result]<>[Comparator])
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 Jul 31, 2017, at 09:16, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
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: david.pratt@outlook.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
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