Romio_1968
Member
- Local time
- Tomorrow, 01:45
- Joined
- Jan 11, 2023
- Messages
- 126
I am using this query to create a table
The table is volatile, so it is recreated and deleted after each use.
The SQLConc functions are doing some data manipulation, bi compressing table fields goruped by a certain criteria into a sigle record (example: inventory numbers of multiple records are compressed into one record, as 1; 2; 5; 9
The problem is that the field is automaticaly formated to Short Text, and that results in a truncated result, since sometimes the length of the concatenation is longer then 255 characters.
This is such a query statemet
The function is
So what should I do in order to format InventoryNumbers field to Long Text?
Thank You.
Code:
SELECT
Titles.Title_ID,
Titles.Title,
SQLConcRow("SELECT Author.Author_Name FROM Author INNER JOIN TAJunction ON Author.Author_ID = TAJunction.Author_IDFK WHERE TAJunction.Title_IDFK = " & [Titles].[Title_ID],", ") AS AuthorNames,
Titles.Timestamp,
Titles.Publisher,
Titles.PublishPlace,
Titles.PrintYear,
Titles.Media,
SQLConcColumn("SELECT TDJunction.ClassCode_FK & ' • ' & Domains.Domain FROM TDJunction INNER JOIN Domains ON TDJunction.ClassCode_FK = Domains.ClassCode WHERE TDJunction.Title_IDFK = " & [Titles].[Title_ID],",") AS ClassCodeDomains,
SQLConcRow("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS InventoryNumbers,
(SELECT Count(*) FROM Inventory WHERE Inventory.Title_IDFK = Titles.Title_ID) AS Supply,
SQLConcRowCompact("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS CompactInventoryNumbers,
Titles.Call_No
INTO
SearchTitleSource
FROM
Titles
ORDER BY
Titles.Title_ID;
The table is volatile, so it is recreated and deleted after each use.
The SQLConc functions are doing some data manipulation, bi compressing table fields goruped by a certain criteria into a sigle record (example: inventory numbers of multiple records are compressed into one record, as 1; 2; 5; 9
The problem is that the field is automaticaly formated to Short Text, and that results in a truncated result, since sometimes the length of the concatenation is longer then 255 characters.
This is such a query statemet
Code:
SQLConcRow("SELECT Inventory.Inventory_No FROM Inventory WHERE Inventory.Title_IDFK = " & [Titles].[Title_ID],",") AS InventoryNumbers,
The function is
Code:
Public Function SQLConcRow(ByVal AnySQL As String, _
Optional ByVal SepF As String = "; ", Optional ByVal SepR As String = "; ", _
Optional ByVal NoNullFields As Boolean = True) As String
Static db As DAO.Database
Static rs As DAO.Recordset
Dim i As Long, Res As String, Tmp As String
If db Is Nothing Then Set db = CurrentDb
Set rs = db.OpenRecordset(AnySQL, dbOpenSnapshot, dbReadOnly)
With rs
Res = ""
Do While Not .EOF
Tmp = ""
For i = 0 To .Fields.Count - 1
If Not (NoNullFields And IsNull(.Fields(i))) Then Tmp = Tmp & SepF & .Fields(i)
Next
If Tmp <> "" Then Res = Res & SepR & Mid(Tmp, Len(SepF) + 1)
.MoveNext
Loop
.Close
End With
If Len(Res) > 0 Then Res = Mid(Res, Len(SepR) + 1)
SQLConcRow = Res
End Function
So what should I do in order to format InventoryNumbers field to Long Text?
Thank You.
Last edited by a moderator: