I have set Allen Browne Concatenate function in my DB. The function combines muchtext multiple rows and yields truncated results.
Truncated field: tum_bulgu_durum_satir.[Tüm Bulgu Durumları]
How can i avoid truncation. My database can be downloaded from following link:
[Moderator edit{removed external link}]
Thank you in advance - Option Compare Database
-
-
Public Function ConcatRelated(strField As String, _
-
strTable As String, _
-
Optional strWhere As String, _
-
Optional strOrderBy As String, _
-
Optional strSeparator = ", ") As Variant
-
On Error GoTo Err_Handler
-
'Purpose: Generate a concatenated string of related records.
-
'Return: String variant, or Null if no matches.
-
'Arguments: strField = name of field to get results from and concatenate.
-
' strTable = name of a table or query.
-
' strWhere = WHERE clause to choose the right values.
-
' strOrderBy = ORDER BY clause, for sorting the values.
-
' strSeparator = characters to use between the concatenated values.
-
'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
-
' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
-
' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
-
' 4. Returning more than 255 characters to a recordset triggers this Access bug:
-
' http://allenbrowne.com/bug-16.html
-
Dim rs As DAO.Recordset 'Related records
-
Dim rsMV As DAO.Recordset 'Multi-valued field recordset
-
Dim strSql As String 'SQL statement
-
Dim strOut As String 'Output string to concatenate to.
-
Dim lngLen As Long 'Length of string.
-
Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.
-
-
'Initialize to Null
-
ConcatRelated = Null
-
-
'Build SQL string, and get the records.
-
strSql = "SELECT " & strField & " FROM " & strTable
-
If strWhere <> vbNullString Then
-
strSql = strSql & " WHERE " & strWhere
-
End If
-
If strOrderBy <> vbNullString Then
-
strSql = strSql & " ORDER BY " & strOrderBy
-
End If
-
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
-
'Determine if the requested field is multi-valued (Type is above 100.)
-
bIsMultiValue = (rs(0).Type > 100)
-
-
'Loop through the matching records
-
Do While Not rs.EOF
-
If bIsMultiValue Then
-
'For multi-valued field, loop through the values
-
Set rsMV = rs(0).Value
-
Do While Not rsMV.EOF
-
If Not IsNull(rsMV(0)) Then
-
strOut = strOut & rsMV(0) & strSeparator
-
End If
-
rsMV.MoveNext
-
Loop
-
Set rsMV = Nothing
-
ElseIf Not IsNull(rs(0)) Then
-
strOut = strOut & rs(0) & strSeparator
-
End If
-
rs.MoveNext
-
Loop
-
rs.Close
-
-
'Return the string without the trailing separator.
-
lngLen = Len(strOut) - Len(strSeparator)
-
If lngLen > 0 Then
-
ConcatRelated = Left(strOut, lngLen)
-
End If
-
-
Exit_Handler:
-
'Clean up
-
Set rsMV = Nothing
-
Set rs = Nothing
-
Exit Function
-
-
Err_Handler:
-
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
-
Resume Exit_Handler
-
End Function
4 2719
For this you can create a workaround by using a function to build a result table like: - Function fncFillConcat()
-
-
Dim rsI As DAO.Recordset
-
Dim rsI2 As DAO.Recordset
-
Dim rsO As DAO.Recordset
-
Dim strTitle As String
-
Dim strTitleOld As String
-
-
' init table
-
CurrentDb.Execute ("Delete * from tblBulgu_Durumu_Result")
-
-
' Select the unique combinations of [Bulgu Kodu] and [Durum Tarihi]
-
Set rsI = CurrentDb.OpenRecordset("Select Distinct [Bulgu Kodu], [Durum Yazan Denetçi] from [Bulgu_Durumu]")
-
Set rsO = CurrentDb.OpenRecordset("tblBulgu_Durumu_Result")
-
-
' Loop to add one row for each IDArtikkel
-
While Not rsI.EOF
-
rsO.AddNew
-
rsO![Bulgu Kodu] = rsI![Bulgu Kodu]
-
'now concatenate all fields
-
Set rsI2 = CurrentDb.OpenRecordset("Select Bulgu_Durumu.[Durum Tarihi] & '|' & Bulgu_Durumu.[Durum Yazan Denetçi] & ' :' as Title , Durum FROM Bulgu_Durumu where [Bulgu Kodu]='" & rsI![Bulgu Kodu] & "' and [Durum Yazan Denetçi]='" & rsI![Durum Yazan Denetçi] & "'")
-
'first place title
-
strTitle = rsI2![Title] & vbCrLf
-
strTitleOld = rsI2![Title] & vbCrLf
-
rsO![Durum] = strTitle
-
While Not rsI2.EOF
-
'Check for title change, when changed, add title
-
strTitle = rsI2![Title] & vbCrLf
-
If strTitle <> strTitleOld Then
-
strTitleOld = strTitle
-
rsO![Durum] = rsO![Durum] & vbCrLf & strTitle
-
End If
-
rsO![Durum] = rsO![Durum] & vbCrLf & rsI2![Durum]
-
rsI2.MoveNext
-
Wend
-
rsO.Update
-
rsI.MoveNext
-
Wend
-
-
-
End Function
-
Just define table "tblBulgu_Durumu_Result" with the fields:
Bulgu Kodu (Text)
Durum (Memo)
and run the function to fill this table.
Nic;o)
Hello Nico,
Thank you for your response.
The code works but it duplicates some [Bulgu Kodu] data.
[Bulgu Kodu] should be unique.
I believe it calculates unique combination as [Bulgu Kodu] & [Durum Yazan Denetçi]. I couldn't fix. Can you look line 13.
Oops, try: - ' Select the unique values of [Bulgu Kodu]
-
Set rsI = CurrentDb.OpenRecordset("Select Distinct [Bulgu Kodu] from [Bulgu_Durumu]")
-
Nic;o)
Thank you very much. Everything works perfect.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: GorDon |
last post by:
Hi,
I have a report based on a query. The query grabs a memo field from
my main table, yet when I display the memo field in the report it
truncates the memo field (the field needs to hold more...
|
by: Anna |
last post by:
Hi all,
I have a query in ASP.NET that selects text from a memo field in Access
(among other things). The query appears to be truncating the text at
255 characters. I've done a little research...
|
by: Laurie |
last post by:
I am using Automation in Access 2003 to open a Word Document and fill
in some values using bookmarks. It all works perfectly except for one
section.
I am filling in some values in a previously...
|
by: lesperancer |
last post by:
I've read all the posts, but nothing works
I've got a table with an ID field and a memo field
one record in the table, with the memo field having 322 chars
I open the table, and then use the...
|
by: jacoballen |
last post by:
I have a query that combines the results of three related tables. The
memo fields are truncated to 255 characters, but I need all the
information in them. I'm aware that removing code such as...
|
by: cherylwalsh35 |
last post by:
I am working in access - using a select Query. I am including a memo field.
The table has all of the data in the memo field displayed - however when I run a select query and include the memo...
|
by: sara |
last post by:
I have a Memo field in a table to hold notes from a conversation a
social worker has had with a client (this is for a non-profit).
If the user needs to update the memo field, I need to find the...
|
by: mccalla |
last post by:
Hi,
I am very new to MS Access. I have inherited an existing app which was done in MS Access 2003. In this app, there is a report which contains a memo field. There are no formats, functions,...
|
by: johnvonc |
last post by:
I am using Access 2003, and trying to Export query results in code to
a csv file.
I am using DoCmd.TransferText. When I run the query, there are two
memo fields: Description and...
|
by: munkee |
last post by:
Right this is purely an idea,
I have an infopath form which I wish to use to populate my access database.
The database and form require the usage of memo fields, basically long text over 255...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |