String too long for Replace function 4

[chetanraina]

Hi folks,

Is there a clean way to run the equivalent of the 'replace' function on a string of greater than 2048 characters? I'm thinking of parsing the string into chunks of less than 2048 characters and running replace on each substring, but I can't find a function that will parse a string into substrings of a certain length.

Thanks!

Chetan

 

[nicsin]

I believe you can use

half1 = left(yourString,len(yourString)/2)

and

half2 = mid(yourString,(len(yourString)/2)+1)

 

[chetanraina]

I'll have to loop through the string cause it could go greater than 4096, but yes the left function is exactly what I was looking for. thanks for the prompt reply!

 

[nicsin]

there is also the

right

function which does exactly the same but gets characters from the right end of the string.

Happy to help and thanx for the star!

 

[MichaelRed]

There is also a "RegExp" function. It will work on the 'longer' strings. See thread705-684231 for a good example, the appropiate reference for hte library and a (brief) explination.

Be aware, however, that RegExp is one of those quite powerful tools and comes with the complexity that the power implies.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CajunCenturion]

"Be aware, however, that RegExp is one of those quite powerful tools and comes with the complexity that the power implies." What a great (and true) statement.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

BTW, you will also find that the RegExp Replace is much faster than instrinsic Replace Function when dealing with long strings.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[chetanraina]

Wow guys, I had no idea that VBA had Regular Expressions in it. When you search the help, there's no mention of it at all. Also, thanks for introducing me to the entire world of references, which I didn't know existed. I wonder how much incredibly useful stuff is in there but is overlooked. Is there a good source for a description of the various references?

Chetan

 

[CajunCenturion]

"Is there a good source for a description of the various references?" The $64K question

Not in one place that I'm aware of. One possible answer is these fora, as collectively, we may have some experience in many of them.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[chetanraina]

Back for more. The ultimate goal for this string is to appear in a form. I have the string going into a textbox, and I was setting the .controlSource field to the value of the string. But this property doesn't like strings longer than 2048. I experimented with the .value and .text properties, with no luck. Any thoughts?

 

[CajunCenturion]

I think I might try using the Microsoft Rich Text Box control. To add it to the form, you need to scroll down through the "More Controls" button on the Controls Toolbar until you find it.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[chetanraina]

Hmm... No luck, it has the same complaint as the regular textbox. My working theory is to try dumping the info using the SendKeys function. Access says that a textbox can hold up to 65535 characters, and I have users entering these long answers into the database through a textbox, so I know that a textbox can hold these answers. It's a shame that all of the VBA properties seem to be limited to String Expressions, which limit you to 2048 characters.

Thank you very much for your help CajunCenturion! Let me know if you have any other thoughts on different ways to attack this.

Chetan

 

[strongm]

>RegExp...we may have some experience

Heh!

> I had no idea that VBA had Regular Expressions

It doesn't, but Microsoft's scripting libraries - which VBA can reference - do

>doesn't like strings longer than 2048

Regular Forms 2 textbox .Text property happily accepts strings much bigger than 2048 characters on my system. I wouldn't necessarily expect .ControlSource to hold anything longer than a legitimate field or cell reference (and I'd expect it to error if I tried to set it to anything that wasn't a cell or field reference)

 

[CajunCenturion]

I'm not sure what you're doing, but I was just able to build a string of 64,000 characters - not 64K (65535) but exactly 64,000 - and place them in a unbound text box and edit.

Perhaps you could try unbinding the textbox, and manually via a recordset, handling the database activity?


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[strongm]

In fact, I'm confused. I don't understand where this 2048 character limit is coming from even in the root post of this thread. I don't have any problems in Access 2000 VBA in running the Replace function against large strings. For example:

wombat = String(64 * 1024&, " &quot ' 64k string
wombat = Replace(wombat, " ", "*&quot

works exactly as expected

 

[TonyJollans]

Hi chetanraina,

The length restriction is on the ControlSource Property, not the Control itself. Using a different type of Control doesn't change that. Textboxes can, indeed, hold up to 64K characters - all you need is a mechanism for getting the data in there.

Could you set up your own Functions which returned the strings and then set the ControlSources to those Functions? You already have the data in variables - if the variables were public you could simply, for example ..

Function TextBox1String() As String
TextBox1String = PublicVariableWithItIn
End Function

and in the control source, put =TextBox1String()

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[chetanraina]

We have a winner! Thank you TonyJollans! That solution worked like a charm. thank you for saving me from another day of banging my head against a wall. As an aside when does the control source re-execute the function?

Strongm, I believe you are right in that replace is not limited to 2048 characters. I just tried it again myself and didn't find any error. I was using the return value from the replace inside an assignment to a control source and stupidly did not isolate the true cause of the error. I apologize for the bad form. Is it possible to ask that the name of this thread be changed so that others won't be confused by it?

Thank you again to everyone who has helped me today.

Chetan

I regret that I have but one star to give to each of you.

 

[TonyJollans]

Hi Chetan,

The function should be executed whenever the form (or the control) is refreshed - either explicitly or by showing it or (if bound) ging to another record.

Glad I could help and thanks for the star.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[CajunCenturion]

I'm still trying to get up off the floor when in a thread, RegExp has been mentioned after which strongm talks about the Replace function :-D

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[strongm]

<innocent whistle>