What Data Type: String of infinite len

[Mario]

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

 

[Guest]

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

 

[Marshall Barton]

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).

 

[Guest]

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]

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]

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.

 

[Dirk Goldgar]

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?

 

[Marshall Barton]

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.

 

[Guest]

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

 

[Dirk Goldgar]

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?

 

[Mario]

You are a genius. I found a work around for that.

Here is anothe problem I am facing

How to insert "Manual Line Break" in querry results.

I have a querry that has the following two columns:
1. StudentID
2. StudentDetails

The column StudentDetails invokes a VBA
function "Concatenate" to obtain the results from two
tables based on StudentID.

Sample Result:

StudentID StudentDetails
1 StudentID:1, NoOfSubjects:2, StudentID--1-
-SubjectID--1--64, StudentID--1--SubjectID--2--85

Desired result:

StudentID StudentDetails
1 StudentID:1,
NoOfSubjects:5,
StudentID--1--SubjectID--1--64
StudentID--1--SubjectID--2--85

Is there any way to insert a special character for line
breaks and tabs (like ^I in MS-WORD for manual line
break) in the querry results (i.e mean inside the VBA
function), so that when I export the query results to MS-
Excel or MS-Word the results appear formatted.

Pls help.

Thanks

-----Original Message-----

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)


.

 

[Duane Hookom]

I believe there is a response to this in another thread by you with the same
question. Please keep your questions in a single thread.

--
Duane Hookom
MS Access MVP
--

You are a genius. I found a work around for that.

Here is anothe problem I am facing

How to insert "Manual Line Break" in querry results.

I have a querry that has the following two columns:
1. StudentID
2. StudentDetails

The column StudentDetails invokes a VBA
function "Concatenate" to obtain the results from two
tables based on StudentID.

Sample Result:

StudentID StudentDetails
1 StudentID:1, NoOfSubjects:2, StudentID--1-
-SubjectID--1--64, StudentID--1--SubjectID--2--85

Desired result:

StudentID StudentDetails
1 StudentID:1,
NoOfSubjects:5,
StudentID--1--SubjectID--1--64
StudentID--1--SubjectID--2--85

Is there any way to insert a special character for line
breaks and tabs (like ^I in MS-WORD for manual line
break) in the querry results (i.e mean inside the VBA
function), so that when I export the query results to MS-
Excel or MS-Word the results appear formatted.

Pls help.

Thanks

-----Original Message-----

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)


.

 

[Dirk Goldgar]

You are a genius. I found a work around for that.

Here is anothe problem I am facing

How to insert "Manual Line Break" in querry results.

I have a querry that has the following two columns:
1. StudentID
2. StudentDetails

The column StudentDetails invokes a VBA
function "Concatenate" to obtain the results from two
tables based on StudentID.

Sample Result:

StudentID StudentDetails
1 StudentID:1, NoOfSubjects:2, StudentID--1-
-SubjectID--1--64, StudentID--1--SubjectID--2--85

Desired result:

StudentID StudentDetails
1 StudentID:1,
NoOfSubjects:5,
StudentID--1--SubjectID--1--64
StudentID--1--SubjectID--2--85

Is there any way to insert a special character for line
breaks and tabs (like ^I in MS-WORD for manual line
break) in the querry results (i.e mean inside the VBA
function), so that when I export the query results to MS-
Excel or MS-Word the results appear formatted.

Tabs probably won't be interpreted correctly, but you can insert the
combination

Chr(13) & Chr(10)

or, in VBA code, the defined constant

vbCrLf

or

vbNewLine

to put a line break where you want it.

 

[Marshall Barton]

Mario wrote:
[snip]

Is there any way to insert a special character for line
breaks and tabs (like ^I in MS-WORD for manual line
break) in the querry results (i.e mean inside the VBA
function), so that when I export the query results to MS-
Excel or MS-Word the results appear formatted.

I don't think there is a control sequence that works in all
those programs, each on has it's own idea of how to
represent a new line. For example, Excel uses Chr(10) and
Access uses Chr(13) & Chr(10).