String function to retun >255 char len result.

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

Lets say I have a querry having the following two fields.

Select StudentID, Concatenate(".." & StudentID & "...")
As StudentInformation.

Concatenate function gets other information for a
particular "studentID" from 3 other tables. In the VBA
Concatenate function is declared as type "String". The
result of concatenate function is about 3000 char in
length.

After the query finishes runing, i see that concatenate
function is returning only 255 characters. But inside the
concatenate function I used a "Msgbox" to see how the
result string is being built dynamically and its
returning more than 255 characters, perfectly as I
expected. But after the querry has run the concatenate
functions is returning only 255 characters.

I tried to import the querry results as table using "File-
Get External Data->querries(with get querries as table,
Click to expand...
option, selected)". In the design mode of the table I see
the field "StudentInformation" is of data type "Text" of
255 char len.

Do i need to declare the function "Concatenate" as a
different type in the VBA.
Do i need to change the change the format of the querry
for field "StudentInformation"

Please help.
 
Mario,

What is it that you are trying to do, returning > 3000 bytes per records
seems like there may be a design problem?

Dan
 
Could you provide the full SQL that uses the Concatenate() function? The
function will return more than 255 characters. You can test this by opening
the debug window (press Ctrl+G) and then entering
? Concatenate("..... 123 ...")
where you use the same expression as your query except you substitute a
legitimate studentID in the sql.
 
Could you provide the full SQL that uses the Concatenate() function?
Click to expand...

It uses the DISTINCT keyword and that seems to be the problem. In
m.p.a.TablesDbDesign group.

Timn F
 
Back
Top