Concatenate single field in report text box

[dbar10]

I have loaded DHookum's Concatenate module and am trying to concatenate a single field from a query into a text field on a preformatted report, without any success. My problem is I just don't get it. can you help?

SELECT [DME POC Listing].POCID, Concatenate("SELECT [DME POC Listing].Description FROM [DME POC Listing]
WHERE POCID=[POC Form].POCID") AS Description
FROM [DME POC Listing];

I mentioned that the results of the query are going into a single line on a official government report. I need to limit the text to the size of the field (about 2.5 inches and run the overflow into another page in the report footer. Is this possible?
I really appreciate you folks being a resource. I have learned a ton with your help. Thank you

 

[lameid]

I'm not sure what the function does but I have a guess. Try...

SELECT [DME POC Listing].POCID, Concatenate("SELECT [DME POC Listing].Description FROM [DME POC Listing]
WHERE POCID = " & [POC Form].POCID) AS Description
FROM [DME POC Listing];

Notice how I embedded the literal from for the field in your string being passed instead of passing the text.

 

[dbar10]

Thanks for your response but that didn't do it. I'm getting an error message that the expression is too complex

 

[lameid]

Can you provide a link to the concatenate code? On the top of this screan is a thread number... if you copy that entire text and past it it would link to this thread. The similar is true for other threads and faqs.

So thread 703 - 1544579 without the spaces looks like thread703-1544579 in a preview or submitted post.

 

[dbar10]

Yes, the following is DHookum's Concatenate function as it appears in the module I created. It's named modConcatenate.


Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'(This SQL statement assumes FamID is numeric)
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'===================================
'
'If the FamID is a string then the SQL would be
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =""" & [FamID] & """") as FirstNames
'FROM tblFamily
'===================================

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
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
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[lameid]

Thinking it through, you should have a group by...

SELECT [DME POC Listing].POCID, Concatenate("SELECT [DME POC Listing].Description FROM [DME POC Listing]
WHERE POCID = " & [POC Form].POCID) AS Description
FROM [DME POC Listing]
Group By [DME POC Listing].POCID;

That being said, it is probably saying it is too complex because of some optimization it is trying to do. The group by might work. Basing this query on a query that returns nothing but group by POCID, might work too.

Barring that, you will probably have to make a table and use code to fill it in. I'm hoping you can avoid this because Access doesn't have a temporary version of tables.

 

[dbar10]

I put in your code, but I can't tell if it works because now I am getting a debug error from the modConcatenate code:
Dim strConcat As String 'build return string
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

The line: If Not .EOF Then is the one highlighted. Any ideas?

 

[lameid]

OH... you see how there are single quotes at the begining of some lines and they are a different color (probably green) in your code window?

Those are comment lines, the compiler doesn't see those. The comments say to uncomment this for DAO or this for ADO... DAO is faster for access tables. ADO is faster for anything else. If you use DAO, you need to got to the tools menu, select references find the refernce for dao and check it.... Microsoft DAO 3.6 Object Library is what I have in Access 2003. The version number may vary for your version of Access and patch level. If you see more than one, select the highest version.

 

[dhookom]

Is this the SQL of your report's record source? If so, it doesn't make much sense sing DME POC Listing is the domain in the function call (child table) and also the main query (parent table).

Is POCID text or numeric?

Your code must use either the ADO or the DAO code. It looks like you have them both commented out.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

YeeHaw!!! That worked. Thank you very much. You guys are incredible.

 

[dbar10]

One more little problem. When I open the report it asking for for the Parameter Value for POC Form.POCID. I have tryed to figure out how to get this to pull but I don't see why it is doing this. Can you help one more time?

 

[lameid]

So you are basing the report on the query here?

If so if the query opens without prompts you have a control or report grouping referencing it.

I guess Technically it could be in your filter (check the where condition of your docmd.openreport).

 

[dbar10]

This is the query:

SELECT [DME POC Listing].POCID, Concatenate("SELECT [DME POC Listing].Description FROM [DME POC Listing] WHERE POCID =" & [POC Form].POCID) AS Description
FROM [DME POC Listing]
GROUP BY [DME POC Listing].POCID;

After I run this I get the debug:
Set rs = db.OpenRecordset(pstrSQL)

 

[lameid]

OOPS. Sorry I missed that you didn't use the table name in your original SQL...

SELECT [DME POC Listing].POCID, Concatenate("SELECT [DME POC Listing].Description FROM [DME POC Listing]
WHERE POCID = " & [red][DME POC Listing][/red].POCID) AS Description
FROM [DME POC Listing]
Group By [DME POC Listing].POCID;

 

[dbar10]

That did the trick lameil. you guys are a great team of people. Thanks again.