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: saigonf7q5@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar