Is there a data type which is like string of infinite
length.

What I am trying to do is pass a parameter into a
function. This function querries some 5 diff table based
on the parameter and concatenates and returns the result
(to a querry).

Any suggestions or examples. Pls help

AFAIK, computers can't recognize such concepts as "infinity" - everything has a length. A variable of subtype string can have up to 2 billion characters in it, depending on what version of VBScript you use

If that doesn't cut it, you can create an array of type string, so that each element of the array is of subtype string, which means you've got 2 billion characters per slot. You'd have to contrive some way to concatenate all that...but I believe there is a limit to the number of elements you can have in array. So even that method would prevent you from having an "infinite" number of characters

----- Mario wrote: ----

Is there a data type which is like string of infinite
length.

What I am trying to do is pass a parameter into a
function. This function querries some 5 diff table based
on the parameter and concatenates and returns the result
(to a querry)

Any suggestions or examples. Pls hel

Mario wrote:

>Is there a data type which is like string of infinite
>length.
>
>What I am trying to do is pass a parameter into a
>function. This function querries some 5 diff table based
>on the parameter and concatenates and returns the result
>(to a querry).


Nothing in a computer is infinite, but a form/report text
box bound to a Memo field can contain 65,535 characters. A
Memo field that is manipulated only using VBA string
variables is limited by the size of the database (2 GB in
recent versions).
--
Marsh
MVP [MS Access]

I am using Microsoft Visual Basic 6.0. On the bottom of
the "about" dialog box it says "VBA: Retail 6.0.8714"

Also I meant a string of significant length may be lets
say 10,000 char and literally not "infinite". I am not a
big computer expert.

Also I measured the length of string returned by the
function, its all 255. I concatenate querry results from
different table and assign it to a string variable. is
that a problem.

How do you declare "A variable of subtype string"

Thanks and pls help

>-----Original Message-----
>AFAIK, computers can't recognize such concepts
as "infinity" - everything has a length. A variable of
subtype string can have up to 2 billion characters in it,
depending on what version of VBScript you use.
>
>If that doesn't cut it, you can create an array of type
string, so that each element of the array is of subtype
string, which means you've got 2 billion characters per
slot. You'd have to contrive some way to concatenate all
that...but I believe there is a limit to the number of
elements you can have in array. So even that method
would prevent you from having an "infinite" number of
characters.
>
> ----- Mario wrote: -----
>
> Is there a data type which is like string of
infinite
> length.
>
> What I am trying to do is pass a parameter into a
> function. This function querries some 5 diff table
based
> on the parameter and concatenates and returns the
result
> (to a querry).
>
> Any suggestions or examples. Pls help
>
>.
>

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.

>-----Original Message-----
>AFAIK, computers can't recognize such concepts
as "infinity" - everything has a length. A variable of
subtype string can have up to 2 billion characters in it,
depending on what version of VBScript you use.
>
>If that doesn't cut it, you can create an array of type
string, so that each element of the array is of subtype
string, which means you've got 2 billion characters per
slot. You'd have to contrive some way to concatenate all
that...but I believe there is a limit to the number of
elements you can have in array. So even that method
would prevent you from having an "infinite" number of
characters.
>
> ----- Mario wrote: -----
>
> Is there a data type which is like string of
infinite
> length.
>
> What I am trying to do is pass a parameter into a
> function. This function querries some 5 diff table
based
> on the parameter and concatenates and returns the
result
> (to a querry).
>
> Any suggestions or examples. Pls help
>
>.
>

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.
>-----Original Message-----
>Mario wrote:
>
>>Is there a data type which is like string of infinite
>>length.
>>
>>What I am trying to do is pass a parameter into a
>>function. This function querries some 5 diff table
based
>>on the parameter and concatenates and returns the
result
>>(to a querry).
>
>
>Nothing in a computer is infinite, but a form/report text
>box bound to a Memo field can contain 65,535
characters. A
>Memo field that is manipulated only using VBA string
>variables is limited by the size of the database (2 GB in
>recent versions).
>--
>Marsh
>MVP [MS Access]
>.
>

"Mario" <(E-Mail Removed)> wrote in message
news:1846101c44a57$353d3370$(E-Mail Removed)
> Is there a data type which is like string of infinite
> length.
>
> What I am trying to do is pass a parameter into a
> function. This function querries some 5 diff table based
> on the parameter and concatenates and returns the result
> (to a querry).
>
> Any suggestions or examples. Pls help

I'm not sure, but I suspect your problem may be in the way your function
expression is being interpreted by the query. Would you mind posting
the code of the function and the SQL of the query that calls it?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

There are several thing that you might have in the query
that will cause the string to be truncated (Group By, Order
By, DISTINCT, etc). Post your query so we can see if that's
part of the issue.
--
Marsh
MVP [MS Access]


Mario wrote:

>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.
>>-----Original Message-----
>>Mario wrote:
>>
>>>Is there a data type which is like string of infinite
>>>length.
>>>
>>>What I am trying to do is pass a parameter into a
>>>function. This function querries some 5 diff table
>based
>>>on the parameter and concatenates and returns the
>result
>>>(to a querry).
>>
>>
>>Nothing in a computer is infinite, but a form/report text
>>box bound to a Memo field can contain 65,535
>characters. A
>>Memo field that is manipulated only using VBA string
>>variables is limited by the size of the database (2 GB in
>>recent versions).

Following is the querry:
SELECT DISTINCT [tblTransmittalTracking].[Date] AS
SendTo_BOE_Date, RecordCount("Select CorrectionsID From
tblTransmittalTracking Where [Sent/Received] Like 'Sent
To' AND Division Like 'BOE-Mapping' AND Date Like #" &
SendTo_BOE_Date & "#") AS NoCorrectionsIDsSent,
ConcatCorrIDErrorID("Select CorrectionsID From
tblTransmittalTracking Where [Sent/Received] Like 'Sent
To' AND Division Like 'BOE-Mapping' AND Date Like #" &
SendTo_BOE_Date & "#") AS CorrectionsIDsSent
FROM tblTransmittalTracking
WHERE ((([tblTransmittalTracking].[Sent/Received])
Like 'Sent To') And (([tblTransmittalTracking].
[Division]) Like 'BOE-Mapping'))
ORDER BY [tblTransmittalTracking].[Date];

------------------------
Following is the function:
Function ConcatCorrIDErrorID(pstrSQL As String, Optional
pstrDelim As String = ", ") As String

'MsgBox (pstrSQL)
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string

With rs
If Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
strConcat = strConcat & "CorrID:" &
rs.Fields(0)
Dim rs_ErrorID As New ADODB.Recordset
Dim strSQL_ErrorID As String
strSQL_ErrorID = "Select Error_ID From
tblCorrectionsInventory Where CorrectionsID=" & rs.Fields
(0)
'MsgBox (strSQL_ErrorID)
rs_ErrorID.Open strSQL_ErrorID,
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs_ErrorID.MoveFirst
strConcat = strConcat & "--ErrorID:" &
rs_ErrorID.Fields(0) & pstrDelim
rs_ErrorID.Close
Set rs_ErrorID = Nothing
rs.MoveNext
MsgBox (strConcat)
Loop
End If
rs.Close
End With
Set rs = Nothing

If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len
(pstrDelim))
End If
ConcatCorrIDErrorID = strConcat
End Function

Please help.
Mario

>-----Original Message-----
>"Mario" <(E-Mail Removed)> wrote in
message
>news:1846101c44a57$353d3370$(E-Mail Removed)
>> Is there a data type which is like string of infinite
>> length.
>>
>> What I am trying to do is pass a parameter into a
>> function. This function querries some 5 diff table
based
>> on the parameter and concatenates and returns the
result
>> (to a querry).
>>
>> Any suggestions or examples. Pls help
>
>I'm not sure, but I suspect your problem may be in the
way your function
>expression is being interpreted by the query. Would you
mind posting
>the code of the function and the SQL of the query that
calls it?
>
>--
>Dirk Goldgar, MS Access MVP
>www.datagnostics.com
>
>(please reply to the newsgroup)
>
>
>.
>

<(E-Mail Removed)> wrote in message
news:1887801c44a79$45b00bc0$(E-Mail Removed)
> Following is the querry:
> SELECT DISTINCT [tblTransmittalTracking].[Date] AS
> SendTo_BOE_Date,
[...]

There's your problem right there. When you use the DISTINCT keyword,
the database engine does internal grouping that limits every text or
memo field (or string expression) to a maximum of 255 characters. Your
function is pobably returning a much longer string than that, but it's
being truncated. Do you need to use the DISTINCT keyword?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)