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 (1) |
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