Senin, 31 Juli 2017

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
 


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
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@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
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 (4)
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.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)