VBA String variable

toleafs

Active Member
Joined
Jun 27, 2005
Messages
498
Hello
Any help is appreciated. I am using the following code to group the ru****s in an query. In the query I have the following field in the query: ReportID: joinReportIDGL([BDDDataElement]). The issue is that the field is cutting off at 255 characters. Is there a way to modify the code to capture more characters>
thanks in advance for your help.

Public Function joinReportIDGL(LN As String) As String
' Groups ReportID in BDDFeed
Dim rs As Recordset
Dim ret As String

Set rs = CurrentDb.OpenRecordset("Select Distinct [Report ID] from tblReportsToCognos where BDDDataElement = """ & LN & """")

rs.MoveFirst
Do While Not rs.EOF
ret = ret & rs![Report ID] & ","
rs.MoveNext
Loop

If rs.recordCount = 0 Then
rs.Close
joinReportIDGL = ""
Else
rs.Close
ret = Left(ret, Len(ret) - 1)
joinReportIDGL = ret
End If
End Function
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,760
Office Version
  1. 2019
Platform
  1. Windows
Not sure. Sometimes the data is there but not visible. String variables are not limited to 255 characters but sometimes Access behaves funny with that kind of data. Occasionally its just the column width!
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349
I tried 3308 symbols - no problem in a select query.

If the query is trying to write to a table - then probably the issue is the length of field (limited to 255).
 

Watch MrExcel Video

Forum statistics

Threads
1,118,189
Messages
5,570,760
Members
412,340
Latest member
nikitesh95
Top