How to have a string longer than 255 chars

[darkmastergyz]

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?

 

[rainman89]

255 char usually

 

[georgedwilkinson]

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]

Is there anyway to get around it....it's blowing up my app right now

 

[darkmastergyz]

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]

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.

 

[georgedwilkinson]

Don't post the same question twice!

http://www.access-programmers.co.uk/forums/showthread.php?t=154582

 

[georgedwilkinson]

I take it you no longer want this question answered since you created a new post.

 

[rainman89]

i dont read well, i thought it was to a text box..... i need to start paying attention

 

[darkmastergyz]

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

 

[georgedwilkinson]

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]

Thanks very much!

This is how I have it:

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

 

[georgedwilkinson]

The watches are limited in how much data they can show.

 

[Banana]

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:

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]

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.

.

 

[CyberLynx]

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]

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]

rneifeld - thanks for the comments. Are you aware that the thread is 10 years old?

 

[rneifeld]

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

 

[The_Doc_Man]

Nothing wrong, just didn't want you to wait for one of the original participants to chime in.