Solved Creating an 'overflow' text box

[alikona]

Hi all,

I have an access database that I use to speed up my data entry into another program (SAP). I use it to concatenate multiple fields into one. One of my issues is that the field in SAP has a character limit of 40 and any other data must then the added to a 'long' text box beneath it. I would like to mimic this in my database so that I am not copying and deleting parts of my data so it fits in the 2 text boxes in SAP.

On my form I have the following fields:

TagNo (textbox with static 6 digit number - formatted as plain text to not cause issues with numbers/text)​

VendoNo (drop down - I pulled a varied length vendor name from this)​

DefectDesc (textbox - varies in length quite a bit)​

​

txtTagDesc (textbox - this is the field I concatenate TagNo, VendorNo and DefectDesc in)​

txtNotTitle (textbox - this is my main 40 limit text)​

txtNotComment (textbox - this is my overflow textbox)​

​

I have the control source of txtTagDesc as:

=[TagNo] & " " & [VendorNo].[column](5) & " " & [DefectDesc]

I have tried the following in the control source for txtNotTitle

=Right([txtTagDesc],40)

I have tried the following in the control source for txtNotComment:

=Mid([txtTagDesc],40)

Now this works but my only issue is that it cuts off exactly at 40 characters. I would like it to cut off after a whole word but I am unsure how to write the code to do that.

For example, I have the following concatenated in txtTagDesc:

177728 BIGGREEN insufficient material impacting part fit​

txtNotTile would have:

177728 BIGGREEN insufficient material im​

txtNotComment would have:

pacting part fit​

I would like it count 40 characters and then go back to the nearest 'space' so that words are kept whole.

Ideally txtNotTile would have:

177728 BIGGREEN insufficient material​

and txtNotComment would have:

impacting part fit​

Any suggestions?

 

[ebs17]

Starting from the 40th character, look for the first space from the right. That would then be the position to separate.

? InstrRev(Left("177728 BIGGREEN insufficient material impacting part fit?", 40), " ")
 38

 

[alikona]

Starting from the 40th character, look for the first space from the right. That would then be the position to separate.

? InstrRev(Left("177728 BIGGREEN insufficient material impacting part fit?", 40), " ")
38

Thank you very much! I got it figured out with with your suggestion.

For anyone else who might stumble on this, I came up with the following. It checks to see if the string in txtTagDesc is greater than 40 via IIF(Len()), if true it then uses the left/mid commands using InStrRev to find out what the length of the string should be. I then added Trim() to the overflow textbox to remove any leading spaces. Without the IIF(Len()) to check if the string was greater than 40 characters, it was cutting off part of the string that it shouldn't have been.

txtNotTitle =

=IIf(Len([txtTagDesc])>40,Left([txtTagDesc],(InStrRev(Left([txtTagDesc],40)," "))),[txtTagDesc])

txtNotComment =

=IIf(Len([txtTagDesc])>40,Trim(Mid([txtTagDesc],(InStrRev(Left([txtTagDesc],40)," ")))),"")