Hi. I have issue with SQL Statement
DoCmd.RunSQL "SELECT [Combined-Tax-Data].LoanID
, Sumtax([LoanID]) AS TaxTotal, ConcatVar([LoanID]) AS Explanation INTO temp_deliquency FROM [Combined-Tax-Data] GROUP BY [Combined-Tax-Data].LoanID
, Sumtax([LoanID]), ConcatVar([LoanID]);"
Data is truncated: If you see Attached Excel File I'm trying to combine all tax explanation. Using above SQL statement Data is truncated: you may from the top to "$94.80, HEALTH/EXTR". Data for $90.87, HEALTH/EXTR
$505.44, EM REPAIR/NEW
Not included into Combined value.
Purpose was to arrange everything from top as array:
$739.74, RENT STAB;$411.50, RENT STAB;$514.37, RENT STAB;$328.81, RENT STAB;$411.01, RENT STAB;$431.56, RENT STAB;$464.80, EM REPAIR/NEW;$122.52, EM REPAIR/NEW;$141.20, INSP;$129.70, INSP;$94.80, HEALTH/EXTR;$94.80, HEALTH/EXTR;$94.80, HEALTH/EXTR;$94.80,"
Question: How can I change VBA Code or SQL Statement to get full data without truncation.
Current data type is text 255. I tried to change data type to memo and append data to exising text field with data type "memo". Not working
Please see file and code data.
Function ConcatVar(sVar As Variant) As String
Dim rs As DAO.Recordset, s As String
Set rs = CurrentDb.OpenRecordset("select * From [Combined-Tax-Data] Where [LoanID]='" & sVar & "'")
Do Until rs.EOF
s = s & rs("[Tax_Explanation]") & ";"
ConcatVar = Left(s, Len(s) - 1)