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:
I have tried the following in the control source for txtNotTitle
I have tried the following in the control source for txtNotComment:
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:
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:
and txtNotComment would have:
Any suggestions?
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:
Code:
=[TagNo] & " " & [VendorNo].[column](5) & " " & [DefectDesc]
I have tried the following in the control source for txtNotTitle
Code:
=Right([txtTagDesc],40)
I have tried the following in the control source for txtNotComment:
Code:
=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?