This is how I would approach the problem assuming SampleID is numeric:
Dim strSQL as String
'Build the dynamic SQL statement
strSQL = ""
strSQL = strSQL & "SELECT [ParameterName] & "", "" & [Units] AS [Parameters], [Result] AS Results, tblSampleResults.Comparator " & vbCrLf
strSQL = strSQL & "FROM lu_tblAnalyses " & vbCrLf
strSQL = strSQL & "INNER JOIN ((tblSampleResults INNER JOIN tblParameters ON tblSampleResults.ParameterID = tblParameters.ParameterID) " & vbCrLf
strSQL = strSQL & "INNER JOIN lu_tblParameterGroups ON tblParameters.ParameterGroupID = lu_tblParameterGroups.ParameterGroupID) " & vbCrLf
strSQL = strSQL & " ON lu_tblAnalyses.AnalysisID = tblParameters.AnalysisID " & vbCrLf
strSQL = strSQL & " WHERE tblSampleResults.SampleID = " & Me![lstSamples] & " " & vbCrLf
If Me.chkLessThan = True Then
strSQL = strSQL & " AND [Result] < [The Detection limit] " & vbCrLf
End If
strSQL = strSQL & "ORDER BY lu_tblParameterGroups.ParameterGroupSort, tblParameters.ParameterSort;"
' assign the SQL statement to the record source of the current form
Me.RecordSource = strSQL
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 31, 2017 12:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] IIF as Query Criteria
Sent: Monday, July 31, 2017 12:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] IIF as Query Criteria
I didn't think you could use mathematical comparisons within the True or False parts of an IIf() function.
When these types of filters get complex, I would write code to create the SQL and then set the form's record source to the SQL statement.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 31, 2017 9:36 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] IIF as Query Criteria
Sent: Monday, July 31, 2017 9:36 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] IIF as Query Criteria
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
| www.viescas.com If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book recommendations and all the cool links! |
On Jul 31, 2017, at 09:16, david.pratt@outlook.com [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: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
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