Concatenating a field

[Guest]

Here is the code I am using to concatenate ( I found this on a post here,
thanks!) a field in a report.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

Here is the field I have in my query for the report
CName: Concatenate("SELECT [Container] FROM [tblContainer] WHERE
[ContainerID] =" & [SalesID])

This works great for most things I need to concatenate, however I have a
report that I want a little bit deeper. I'm not very good at SQL type
statements so bear with me. I want the cancatenate field to not only group
where the ContainerID = the SalesID, but then also show only the containers
that are in the sections I have listed in my report. Like SalesID 1 shows
section 1, 2 & 3. Right now, in section 1, 2, & 3 it shows ALL of the
containers for that SalesID. I was wondering if there some sort of "AND"
statement that I could add to the end of the field in the query that would
concatenate it by the first group and then by the second and only show the
containers that go with each section. Hope that makes sense!

Thanks for any help you can give me.

 

[Duane Hookom]

I'm not sure I understand your tables/fields/report but you might be able to
CName: Concatenate("SELECT [Container] FROM [tblContainer] WHERE
[ContainerID] =" & [SalesID] & " AND [GroupID] = " & [GroupID])

Keep in mind that if GroupID is text, you would need something like:

CName: Concatenate("SELECT [Container] FROM [tblContainer] WHERE
[ContainerID] =" & [SalesID] & " AND [GroupID] = """ & [GroupID] & """")

 

[Guest]

That worked great! I had tried a few things, but didn't quite have the & "
AND in the right place. Thanks much!

I'm not sure I understand your tables/fields/report but you might be able to
CName: Concatenate("SELECT [Container] FROM [tblContainer] WHERE
[ContainerID] =" & [SalesID] & " AND [GroupID] = " & [GroupID])

Keep in mind that if GroupID is text, you would need something like:

CName: Concatenate("SELECT [Container] FROM [tblContainer] WHERE
[ContainerID] =" & [SalesID] & " AND [GroupID] = """ & [GroupID] & """")

--
Duane Hookom
MS Access MVP
--

Here is the code I am using to concatenate ( I found this on a post here,
thanks!) a field in a report.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

Here is the field I have in my query for the report
CName: Concatenate("SELECT [Container] FROM [tblContainer] WHERE
[ContainerID] =" & [SalesID])

This works great for most things I need to concatenate, however I have a
report that I want a little bit deeper. I'm not very good at SQL type
statements so bear with me. I want the cancatenate field to not only
group
where the ContainerID = the SalesID, but then also show only the
containers
that are in the sections I have listed in my report. Like SalesID 1 shows
section 1, 2 & 3. Right now, in section 1, 2, & 3 it shows ALL of the
containers for that SalesID. I was wondering if there some sort of "AND"
statement that I could add to the end of the field in the query that would
concatenate it by the first group and then by the second and only show the
containers that go with each section. Hope that makes sense!

Thanks for any help you can give me.