Jumat, 26 Februari 2016

Re: [MS_AccessPros] IIF function

 

Phucon-


Your IIf expressions need to be in the SELECT clause or as expressions in your report.  I recommend you keep your query simple, but add aliases to your fields to make them easier to reference:

SELECT DISTINCT T1.COMPANY, T1.COMMENT AS T1Comment, T2.COMMENT As T2Comment

FROM qry1 AS T1 LEFT JOIN qry2 AS T2 ON T1.COMPANY = T2.COMPANY;

In the text box for your comment from the first table, use:

=IIf([T1Comment] LIKE "#Q####*", [T1Comment], "")

That LIKE predicate tests for numbers in the 1st and 3rd through 6th positions, a Q in the second position, and any trailing characters.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Feb 26, 2016, at 5:29 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Some of the rows of 2 columns, or 2 fields of my query out-put contain incorrect information. I was asked to remove or hide those erroneous to display from the report.

 


I first tried to filter it, but that removed the entire row, resulted in missing records. I then tried the IIF function to put a blank on that field if there's incorrect data there.

 


The correct data should show "1Q2010" (first quarter of the year), "2Q2010", "3Q2010", "1Q2011" … etc… So when the field displays something other than the information of the quarter then remove or hide it.

 


First thing I am not sure where to place the function. Should the function be placed in the query or in the report?  Below is what I've tried within the query. However the result is not quite right. It seems it's not replacing the error with a blank space, it simply removed some records from the query result, included those records that contain incorrect information.

 


p.s. The queries' consist of read only tables. I've tried to make the query into a single table then create a new query and work on the query again, however result is the same (missing records, not replacing blank space to the bad data.)

 


Please help. Thank you.

 


Phucon

 


Below is my query.

 


SELECT DISTINCT T1.COMPANY, T1.COMMENT, T2.COMMENT

FROM qry1 AS T1 LEFT JOIN qry2 AS T2 ON T1.COMPANY = T2.COMPANY

WHERE T1.COMMENT=IIf([T1].[COMMENT] Not Like "1Q*","",[T1].[COMMENT]) OR T1.COMMENT=IIf([T1].[COMMENT] Not Like "2Q*","",[T1].[COMMENT]) OR T1.COMMENT=IIf([T1].[COMMENT] Not Like "3Q*","",[T1].[COMMENT]) OR T1.COMMENT=IIf([T1].[COMMENT] Not Like "4Q*","",[T1].[COMMENT])

AND

T2.COMMENT=IIf([T2].[COMMENT] Not Like "1Q*","",[T2].[COMMENT]) OR T2.COMMENT=IIf([T2].[COMMENT] Not Like "2Q*","",[T2].[COMMENT]) OR T2.COMMENT=IIf([T2].[COMMENT] Not Like "3Q*","",[T2].[COMMENT]) OR T2.COMMENT=IIf([T2].[COMMENT] Not Like "4Q*","",[T2].[COMMENT])

 



__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

.

__,_._,___

Tidak ada komentar:

Posting Komentar