String function to retun >255 char len result.

[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,

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.

 

[solex]

Mario,

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

Dan

 

[Duane Hookom]

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.

 

[Tim Ferguson]

Could you provide the full SQL that uses the Concatenate() function?

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

Timn F