RE: Concatenate and Null Values

Sure thing Duane. Thanks for getting back to me. Here is the complete SQL
View of the my Select Query (Flag Audits Small Query):

SELECT [Flag Audits].[Audit number], [Flag Audits].[Audit Date], [Flag
Audits].[Flag Style], [Flag Audits].[Item Number], [Flag
Audits].[Pass/Repair/Reject 1], [Flag Audits].[Repair/Reject Comments 1],
IIf([Pass/Repair/Reject 1]="Repair",[Repair/Reject Comments 1],"") AS
Repair1, Concatenate("SELECT IIF([Pass/Repair/Reject 1]
='Repair',[Repair/Reject Comments 1],Null) & (IIF([Pass/Repair/Reject 2]
='Repair',[Repair/Reject Comments 2],Null) + ', ') & (IIF([Pass/Repair/Reject
3] ='Repair',[Repair/Reject Comments 3],Null) + ', ') &
(IIF([Pass/Repair/Reject 4] ='Repair',[Repair/Reject Comments 4],Null) + ',
') & (IIF([Pass/Repair/Reject 5] ='Repair',[Repair/Reject Comments 5],Null) +
', ') FROM [Flag Audits] WHERE ( [Pass/Repair/Reject 1] = 'Repair' OR
[Pass/Repair/Reject 2] = 'Repair' OR [Pass/Repair/Reject 3] = 'Repair' OR
[Pass/Repair/Reject 4] = 'Repair' OR [Pass/Repair/Reject 5] = 'Repair' ) AND
[Audit Number] = " & [Audit Number]) AS conRepair1, IIf([Pass/Repair/Reject
1]="Reject",[Repair/Reject Comments 1],"") AS Reject1, Concatenate("SELECT
IIF([Pass/Repair/Reject 1] ='Reject',[Repair/Reject Comments 1],Null) &
(IIF([Pass/Repair/Reject 2] ='Reject',[Repair/Reject Comments 2],Null) + ',
') & (IIF([Pass/Repair/Reject 3] ='Reject',[Repair/Reject Comments 3],Null) +
', ') & (IIF([Pass/Repair/Reject 4] ='Reject',[Repair/Reject Comments
4],Null) + ', ') & (IIF([Pass/Repair/Reject 5] ='Reject',[Repair/Reject
Comments 5],Null) + ', ') FROM [Flag Audits] WHERE ( [Pass/Repair/Reject 1] =
'Reject' OR [Pass/Repair/Reject 2] = 'Reject' OR [Pass/Repair/Reject 3] =
'Reject' OR [Pass/Repair/Reject 4] = 'Reject' OR [Pass/Repair/Reject 5] =
'Reject' ) AND [Audit Number] = " & [Audit Number]) AS conReject1, [Flag
Audits].[Pass/Repair/Reject 2], [Flag Audits].[Repair/Reject Comments 2],
IIf([Pass/Repair/Reject 2]="Repair",[Repair/Reject Comments 2],"") AS
Repair2, IIf([Pass/Repair/Reject 2]="Reject",[Repair/Reject Comments 2],"")
AS Reject2, [Flag Audits].[Pass/Repair/Reject 3], [Flag
Audits].[Repair/Reject Comments 3], IIf([Pass/Repair/Reject
3]="Repair",[Repair/Reject Comments 3],"") AS Repair3,
IIf([Pass/Repair/Reject 3]="Reject",[Repair/Reject Comments 3],"") AS
Reject3, [Flag Audits].[Pass/Repair/Reject 4], [Flag Audits].[Repair/Reject
Comments 4], IIf([Pass/Repair/Reject 4]="Repair",[Repair/Reject Comments
4],"") AS Repair4, IIf([Pass/Repair/Reject 4]="Reject",[Repair/Reject
Comments 4],"") AS Reject4, [Flag Audits].[Pass/Repair/Reject 5], [Flag
Audits].[Repair/Reject Comments 5], IIf([Pass/Repair/Reject
5]="Repair",[Repair/Reject Comments 5],"") AS Repair5,
IIf([Pass/Repair/Reject 5]="Reject",[Repair/Reject Comments 5],"") AS Reject5
FROM [Flag Audits]
WHERE ((([Flag Audits].[Audit Date]) Between [forms]![DateRange]![StartDate]
And [forms]![DateRange]![EndDate]))
ORDER BY [Flag Audits].[Flag Style];


"Duane Hookom" wrote:

Could you share your Concatenate(....) or better yet the entire SQL view of
your query?
--
Duane Hookom
Microsoft Access MVP


"Hoopster" wrote:

I have been able to use the sample Concatenate code supplied by Duane Hookom
in a Module and a Select Query using some Code supplied with the help of John
Spencer to Concatenate many Records into a single Column named conRepair1. My
data in my Query looks like this:
Flag Style conRepair1
2 1/2 X 4 Poly Cotton
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. for CVS 6 stripe to long,
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. for CVS
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Nylon U.S. Sub Assembly grommet to low,
3 X 5 Nylon U.S. Sub Assembly field join & 7 stripe uneven,
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Nylon U.S. Sub Assembly grommet to close
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Nylon U.S. Sub Assembly field join -7 stripe,
3 X 5 Nylon U.S. Sub Assembly
3 X 5 Poly Cotton
3 X 5 Poly Cotton
3 X 5 Reliance U.S. Sub Assembly
3 X 5 Reliance U.S. Sub Assembly
3 X 5 Tough - Tex Sub Assembly raw edge on fh, raw edge on fh,
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner
30" X 48" Poly Cotton Banner label not attached,
30" X 48" Poly Cotton Banner
4 X 6 Super Tough Brand U.S. fly hem, field join - 7 - stripe,

As you can see, not all Records for a certain Flag Style has a
corrisponding conRepair1 Record. When I try to Concantenate the conRepair1
Comments on a Report for each Flag Style, not all Comments are displayed. Can
someone help me to understand what I am missing / doing wrong. Is it because
of the Null Values in the conRepair1 Column? Should I try to Group By Flag
Style in my Query?

.

Relevant Pages

  • Re: how to link to a csv file with a date field in it?
    ... Regarding the scheduler it's in XP and above - if you aren't familiar with using the Windows Task Scheduler, here is a link to a fairly clean, simple, short tutorial. ... I think you have the right idea - link to the csv, maybe run an append query to process the contents into your main table, in that query you can fix things such as the date. ... s is the complete row of data as a string, flag is a boolean that is initialized to be False. ... The full code I snipped this from contains so much checking for quotes and # hashes and internal commas and internal single quotes replacing certain chars, that it's difficult for me to follow and I wrote it. ...
    (microsoft.public.access.externaldata)
  • Re: how to link to a csv file with a date field in it?
    ... I think you have the right idea - link to the csv, maybe run an append query to process the contents into your main table, in that query you can fix things such as the date. ... s is the complete row of data as a string, flag is a boolean that is initialized to be False. ... We can create a recordset object and set it to a specific table - we add the new row one field at a time, testing each field for type using a select case, add the # for dates or ' for text or nothing for numeric field types to the fldArrrayelement contents and the set rs.fields= the resulting expression. ... The full code I snipped this from contains so much checking for quotes and # hashes and internal commas and internal single quotes replacing certain chars, that it's difficult for me to follow and I wrote it. ...
    (microsoft.public.access.externaldata)
  • RE: Concatenate and Null Values
    ... "Duane Hookom" wrote: ... I went to the Design Screen of my Flag Audits Table but it ... I've been working with a Union Query all day but I can't get anything ...
    (microsoft.public.access.reports)
  • Re: MT Data Retrieval Question
    ... maybe the original query, i.e. IQueryable). ... method that you pass as the callback, and you hold onto that object. ... In the abort method, you check the flag. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: unwanted duplicate loan account numbers
    ... I have rewritten your query a bit just to help me study it: ... inactive flag], ... you say there is a duplicated [loan acct #] in this query's results. ... Now, if you have such a duplicate in one column, the other columns may ...
    (microsoft.public.access.queries)