How to have a string longer than 255 chars (2 Viewers)

darkmastergyz

Registered User.
Local time
Today, 05:05
Joined
May 7, 2006
Messages
85
Max length of a string?

I have a string in VBA (it's dim as string), and it's truncating everything longer than 252 characters or so... Is there a length limit on a string?
 
Local time
Today, 07:05
Joined
Mar 4, 2008
Messages
3,856
Hmmm. I've had VB strings much longer than 255 bytes. What are you doing with the string?

I have had problems copying data longer than 255 bytes from Access to Excel, but it is not because of the limitation of a String's size.
 

darkmastergyz

Registered User.
Local time
Today, 05:05
Joined
May 7, 2006
Messages
85
Is there anyway to get around it....it's blowing up my app right now :(
 

darkmastergyz

Registered User.
Local time
Today, 05:05
Joined
May 7, 2006
Messages
85
I have a string right now which needs to hold about 1000 characters or so. But, VBA limits it to only 255 characters. What can I do to make it hold more? (the string holds a SQL string which then gets passed to: db.openrecordset(sqlstr, dbopensnapshot)


Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:05
Joined
Feb 28, 2001
Messages
26,996
Strictly speaking, I don't recall that VBA limits string size to 255. I thought it was 65535 or something like that. But some of the interfaces that pass strings DO limit string size. If you could create an SQL query separately and THEN open it by name, that might be one workaround. I think you can build a long query, you just can't pass one.
 
Local time
Today, 07:05
Joined
Mar 4, 2008
Messages
3,856
I take it you no longer want this question answered since you created a new post.
 

rainman89

I cant find the any key..
Local time
Today, 08:05
Joined
Feb 12, 2007
Messages
3,016
i dont read well, i thought it was to a text box..... i need to start paying attention
 

darkmastergyz

Registered User.
Local time
Today, 05:05
Joined
May 7, 2006
Messages
85
Umm.... I'm not doing anything to it right now. If I just have a string, and assign it a value:

eg.

dim str1 as string
str1 = "[something 252 characters]"

It doesn't hold anything past the first 252 characters....
 
Local time
Today, 07:05
Joined
Mar 4, 2008
Messages
3,856
Can you post your entire code, including whatever code calls the code in question, if pertinent to the problem? I'd really love to see this resolved for you.
 

darkmastergyz

Registered User.
Local time
Today, 05:05
Joined
May 7, 2006
Messages
85
Thanks very much!

This is how I have it:

Code:
dim var1 as string
var1 = "PUT A LONG LONG THING HERE"

when I look at the var1 under watches, it doesn't show the whole thing
 

Banana

split with a cherry atop.
Local time
Today, 05:05
Joined
Sep 1, 2005
Messages
6,318
As mentioned earlier, some methods may truncate to first 255 characters, but the VBA string is definitely capable of holding much more than 255. This says that string can hold up to 2 billion characters (e.g. limited by a Long Integer, presumably.)

But! Text data type you used in table design only can hold up to 255 character. Using Memo will allow up to what I assume is limited by Integer (65,535 characters). Thanks, Allen..

Therefore, the effective limit is 65,535 characters per memo column, though VBA is perfectly capable of holding much more than that.

Also, I suspect that Immediate Windows will also truncate string if one used debug.print, but will have to test this.

You would be better off by testing something like this:

Pseduocode:

Code:
Dim stringer As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset ("SomefancypantqueryReturningAColumnOfMemo")

If Not rst.BOF And Not rst.EOF Then
     stringer=rst.Fields(0)
     Debug.Print Len(stringer) 'How long is this string?
     Debug.print stringer 'See if we can read the whole string
End If
 

CyberLynx

Stuck On My Opinions
Local time
Today, 05:05
Joined
Jan 31, 2008
Messages
585
Code:
dim var1 as string
var1 = "PUT A LONG LONG THING HERE"

MsgBox "String Length =  " & Len(var1) & vbCr & vbCr & var1

Even though the MsgBox will also truncate real long strings, it should be good enough to view most SQL Statements.

.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:05
Joined
Jul 9, 2003
Messages
16,244
Code:
Even though the MsgBox will also truncate real long strings, it should be good enough to view most SQL Statements.

.[/quote]


I always use message boxes to view my SQL strings, very useful and I haven't had any problems yet.  

[COLOR=Red][B]Good Tip:[/B][/COLOR]
Set up a module (I usually name it basConstants) and add the following constant: 
[code]Public Const conShowMessage As Boolean = False
'''' Usage ''' If conShowMessage Then MsgBox " >>> " & strBuildSQL
Now whenever you want to use a message box to show information about how your programming is working, so you can see SQL strings and the like, present the message box in the "If" statement as shown above:

Code:
If conShowMessage Then MsgBox " >>> " & strBuildNumb
then all you have to do is opened the module, change the false to true or vice versa and this will switch on, or off, the message boxes you have strategically placed in your code for debugging.
 
Last edited:

CyberLynx

Stuck On My Opinions
Local time
Today, 05:05
Joined
Jan 31, 2008
Messages
585
If I recall (and don't quote me on it) the MsgBox can display 1024 Characters before things get truncated. I have had extensive SQL strings truncated within the MsgBox but for the most part....it's fine.

.
 

rneifeld

New member
Local time
Today, 05:05
Joined
May 28, 2018
Messages
2
In Excel VBA, the immediate window is truncating a string variable containing more than 255 characters, to the first 255 characters. A Msgbox however, shows the entire string (which is more than 255 characters).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:05
Joined
Feb 28, 2001
Messages
26,996
rneifeld - thanks for the comments. Are you aware that the thread is 10 years old?
 

rneifeld

New member
Local time
Today, 05:05
Joined
May 28, 2018
Messages
2
Doc, I am not sure how to respond. I found this thread useful and so I took the time to add my two cents. Best, r
 

Users who are viewing this thread

Top Bottom