Senin, 31 Juli 2017

Re: [MS_AccessPros] IIF as Query Criteria

 

All of the sudden my form is working again and I can try some things.  I see immediately that the "result if false" needs to be Like "*".



---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> 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 PM
Subject: [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 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: david.pratt@outlook.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (12)
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:

Re: [MS_AccessPros] IIF as Query Criteria

 

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 PM
Subject: [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 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: david.pratt@outlook.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (11)
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:

Re: [MS_AccessPros] IIF as Query Criteria

 

Yes John, I have compacted and repaired three times now and with no joy.  Each time I start the application up the form works just fine in Form View.  However, after opening in Design View, and then trying to go to Form View, the form closes on it's own.  It is as if I have some Event Code under Form_Open that is closing the form.  I have no Form_Open event code and the only Form Event code present is your Form_Error code. 

And I have gone back and completely removed the Criteria code I have been working on, and even still, the form closes when I try to put it in Form View.


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

Did you try compacting and repairing the database?  It sounds like the form has become corrupted.

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 12:10, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


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
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
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 (10)
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:

[belajar-access] File - Tata Tertib dan Aturan.txt

 


Tata tertib dan peraturan
Mailing List: belajar-access@yahoogroups.com

1. Mailing list ini membahas mengenai pemrograman Microsoft Access.
2. Tidak diperkenankan mem-posting topik yang tidak ada kaitannya sama sekali dengan pemrograman MS Access, peluang kerja atau tawaran kerja sama dengan keahlian di bidang MS Access, atau pengajaran/kursus MS Acces. Pelanggaran terhadap aturan ini akan di-ban dari keanggotaan milis ini.
3. Mohon berdiskusi dengan baik, dengan semangat membangun, demi kemajuan kita bersama. Hindarilah perbantahan (flame) yang bisa menjadi pertengkaran yang tidak perlu.
4 Hindari reply permintaan one-liner seperti 'saya minta juga dong', 'saya setuju', dan lain-lain yang tidak perlu.
5. Sedapat mungkin memberikan data-data yang lengkap dalam mengajukan suatu masalah untuk memudahkan rekan-rekan sesama member mengidentifikasi dan mencarikan solusi, termasuk memberikan subject yang sesuai dengan isi email, tidak dengan kata-kata seperti "tologing dong", "pusing...", "ada yang bisa bantu..", dll.

Moderator

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic ()
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.
SPAM IS PROHIBITED

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[AccessDevelopers] File - Monthly_Notices.txt

 


Monthly notices:

Hi Kids!:

Don't forget to check out our "Links" section at the website for helpful sites. Also take a peek at books that others have found worthwhile in our books database under the 'Database' link of the main AccessDevelopers page. Feel free to add any books or links that you have found useful.

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (157)
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.

Please zip all files prior to uploading to Files section.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] Strange Errors when running query with a public function

 

hi Jim,

Access optimizes the query and saves it. Some of the ones you don't recognize are knocked out by the criteria but not all. 

Lots of information might be stored in Flags -- once you figure out why they are there, you can perhaps see a pattern but it might be in a different field too.  Much of what I have figured out about the undocumented stuff is by experimentation on what I had to look at it with. I am not looking at your database, so I don't know what has been done ... but ...

Do you have multi-value or attachment fields or other complex data types?

respectfully,
crystal
 
~ have an awesome day ~

On 7/31/17 11:40 AM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] wrote:
I changed the query to the following SQL below and it worked with the new code. I am still confused what those queries are doing there. they do not show up in the nav pane.


SELECT msysObjects.Name, GetSQL([Name]) AS [SQL], Left([SQL],(InStr(1,[SQL]," ")-1)) AS [Query Type], "Drivers License Database" AS [Database Name], msysObjects.Flags
FROM msysObjects
WHERE (((msysObjects.Name) Not Like '~*') AND ((msysObjects.Flags) Not Like -2147352320 And (msysObjects.Flags) Not Like -2147090176) AND ((msysObjects.type)=5));


Jim Wagner



On Mon Jul 31 2017 09:35:26 GMT-0700 (US Mountain Standard Time), Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 
Crystal,

So I am really trying to figure this out. All 28 or so databases use the original code and query with no problem. This last database I have created a new database and moved all objects to it, I have done all that has been suggested and still it is not working. But after trying your suggestion of the flag field. I created a new query with just the name and the flag field. I noticed something new. everyone of the queries that have this long weird character string making a duplicate has one interesting characteristic. The flag field has a value of -2147352320 and the query that is the correct one has a value in the flag field as 262144.

Jim Wagner



On Sat Jul 29 2017 07:35:28 GMT-0700 (US Mountain Standard Time), crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

here is a more complete list of the Flags*:

a_QryTypes
QryTypI QTyDescr VbaConst
0 Select query dbQSelect
16 Crosstab query dbQCrosstab
32 Delete query dbQDelete
48 Update query dbQUpdate
64 Append query dbQAppend
80 Make-table query dbQMakeTable
96 DDL, Data-definition language query dbQDDL
112 Pass-through query dbQSQLPassThrough
128 Union (Set operation) query dbQSetOperation
144 Bulk operation query dbQSPTBulk
160 Compound query dbQCompound
224 Stored procedure dbQProcedure
240 Action query dbQAction

... I am guessing that 8 (not in list above) is Hidden. Not sure if Flags uses bit-math.

* this table came from the Analyzer, which is a free download and now on GitHub:

https://github.com/strive4peace/Analyzer

~crystal

On 7/29/17 9:14 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:
Hi Jim,

perhaps the problem is recurrent use of CurrentDb ... perhaps try:

Option Compare Database
Option Explicit

Dim m_db As dao.Database

Public Function GetSQL(strQueryName As String) as string
's4p
       If m_db Is Nothing Then
          Set m_db = CurrentDb
       end if
      GetSQL = m_db.QueryDefs(strQueryName).SQL & ""
End Function

public sub ReleaseTheDatabase
   Set m_db = Nothing
end sub

~~~~
then when the query is done, run ReleaseTheDatabase

I changed the return to a string because if Null is returned, your QueryType equation will have an error with Left -- maybe change to Left([SQL] & "  " ...

However, maybe you want to get the query type another way?

use the Flags field of MSysObjects to determine query type

for instance:
0 = Select
32 = Delete
48 = Update
64 = Append
80 = MakeTable
128 = Union

other bits:
8 = Hidden?

respectfully,
crystal
 
~ have an awesome day ~


On 7/28/17 11:00 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] wrote:

Jim,


The msysobjects table isn't documented by Microsoft although many of us have used it. There could be near duplicates from internal processes that we have no idea about.


I'm not sure what you mean by "How does a query run but have errors?"

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, July 28, 2017 3:57 PM
To: Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Strange Errors when running query with a public function
 


Duane,

In database 2 I figured out why the error comes up. But confused at why it gives the error. Apparently there is a macro and it runs a series of queries. 2 of the queries are the ones with ambiguous joins. Although they run in the macro and alone. But will not let me open the queries in design view because of the ambiguous join error. How does a query run but have errors?

I changed the joins and they worked for the second database.


But the first database I am getting a list of the queries but they are being duplicated like the following

qry6To7Points
qry6To7Points_F31DBFE61EC14C9CBA6BB5A50544BA69

qry8OrMorePoints
qry8OrMorePoints_951765DA04F142C6BB3DF1CE020234EC

Jim Wagner



On Fri Jul 28 2017 09:43:29 GMT-0700 (US Mountain Standard Time), Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 
Duane,

I did as you instructed for both databases and received the same errors respectively for both databases.

Could the issue actually be the query syntax of each of the queries?

Is there another sql statement that I do not need the module?

Jim Wagner



On Fri Jul 28 2017 07:43:31 GMT-0700 (US Mountain Standard Time), Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Jim,


This sounds like some form of corruption. I would make a backup of the database and then Repair and Compact. If the errors still occur, create a new database and import all of the objects into it.


Regards,

Duane



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, July 28, 2017 9:03 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Strange Errors when running query with a public function
 


Hello all,


This morning I have come across 2 strange errors that only happens on 2 different databases for a process that is on 20 other databases and work correctly.


I am not sure why these two databases are causing the issues.


I would appreciate any assistance with this crazy problem.


Thank You


Jim Wagner




I have a public function


Option Compare Database


'SQL Database statements


Public Function GetSQL(strQueryName As String)
'GetSQL = CurrentDb.QueryDefs(strQueryName).SQL
End Function


Then a query

SELECT msysObjects.Name, GetSQL([Name]) AS [SQL], Left([SQL],(InStr(1,[SQL]," ")-1)) AS [Query Type], "Drivers License Database" AS [Database Name]
FROM msysObjects
WHERE (((msysObjects.Name) Not Like '~*') AND ((msysObjects.type)=5));


Database 1


The error for this database

is a run-time error 3265

Item not found in this collection.


_________________________________________


Database 2

The next database has the same public function and the same query


but the error is different


it is  run-time error '3258'

The SQL statement could not be executed because it contains  ambiguous joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include the query in your SQL statement.


It does not let me out of the error.














__._,_.___
Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (10)
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:
Langganan: Postingan (Atom)