Increase character limit on long text box

[WillM]

Good afternoon,
I have a long text box on a form (nvarchar(max) on sql server back end) that I need to be able to handle unlimited text input. Currently it can only handle around 160,000 characters.

As I inherited this part of the db, I am not sure how the previous person made the box (the previous version was a zoom box) able to handle the amount of text that it has in it. Unfortunately, the text box errors out from time-to-time and drops user input.

I have searched and not been able to find any type of coding that allows for the increased character limit of a normal long text box. Everything I have read to this point shows it should be able to handle an enormous amount of data, however it stops at roughly 160k characters.

It is an unbound text box currently.

Any help is appreciated,
Will

 

[Ranman256]

why would you need more characters?
If so store data in a binary field, or OLE (word document)

 

[isladogs]

I'm not sure why you think there is a limit of 160,000 characters
For example, the textbox below contains almost 500,000 characters though obviously I need to use the vertical scrollbar

You mentioned the data comes from SQL server
For long text fields, I use the TEXT datatype in SQL server which is equivalent to the memo / long text field in Access

I never use nvarchar so may well be wrong on this but I thought the limit for nvarchar was 255 (as is the case for varchar?)

 

[WillM]

I'm not sure why you think there is a limit of 160,000 characters
For example, the textbox below contains almost 500,000 characters though obviously I need to use the vertical scrollbar

That's the issue - the text field will not take more than that. I had the field in SQL set to ntext, then changed it to nvarchar(max) as I read that ntext was deprecated and that nvarchar(max) was the correct field type to use.

Can I ask how you made your window in your post? Was that a zoombox or regular textbox?

Thanks

 

[isladogs]

That's the issue - the text field will not take more than that. I had the field in SQL set to ntext, then changed it to nvarchar(max) as I read that ntext was deprecated and that nvarchar(max) was the correct field type to use.

Can I ask how you made your window in your post? Was that a zoombox or regular textbox?

Thanks

Hi

I was just going to correct my last post after looking up the max size of varchar, nvarchar & text.
I also spotted that text will be deprecated in a future version of SQL server so that's something I'll need to alter in my to do list.

It's a regular textbox. AFAIK you can't change the SIZE of a zoom box though you can change the font size used in it.

 

[WillM]

Okay - I can't figure out what is wrong with it then. I have the box set to long text on the access side, nvarchar(max) on the SQL side, and it will only take the approx 160k characters and no more.

For your box that has almost 500k in it, you have the same settings?

The reason I need a lot of space is because we have to do a lot of documentation in this database. Every time there is a repeat visit, we have to pull forward the previous documentation into this textbox and combine it with the new documentation.

We approach large numbers very quick. I am open to alternate ideas as well.

Thanks again,
Will

 

[isladogs]

The example shown isn't for a linked SQL server table.
However, I can't see why it would make any difference if it was...

Attached is a simple database used to display & then copy / paste the exact text example I showed you earlier. It was originally written by another forum user (JHB) to solve an issue with moving to the end of a very long text field.

Have a look in case it helps

 

[Mark_]

Will,
Why would they want to repeat the same text in your database?

In a more normal approach I'd have multiple text entries for a customer/work order/what ever and avoid this type of issue.

 

[missinglinq]

Sorry, but every visit needs to have its own entry...pulling forward/entering data from previous visits into the current visit violates the normalization rules that are the heart of relational databases.

Linq ;0)>

 

[Pat Hartman]

I agree with missinglinq. I think you need to change your table design to make each entry a separate row.

However, back to the issue at hand - did you relink the table after you changed the column definition? Perhaps Access still things it is the previous data type.

 

[JHB]

Good afternoon,
I have a long text box on a form (nvarchar(max) on sql server back end) that I need to be able to handle unlimited text input. Currently it can only handle around 160,000 characters.
...

If you're talking about typing in (or copy/paste) manually directly in the control (or in the field in the table) then it can't handle more as 65,535 characters, but if you do it programmatically, ex. copy from another control and added to the control, then you're only limit by the size of Memo (Long Text) field, which is 1GB.
Taken from the MS-Access 2016 specifications:

Number of characters in a Long Text field:

65,535 when entering data through the user interface;
1 gigabyte of character storage when entering data programmatically
​

 

[WillM]

Thank you everyone - I think I have figured out a work around that might alleviate the issue.

The "need" for the repeating text is because the report is required to have the most recent documentation prior to adding any new documentation. Unfortunately this can lead to longer text boxes.

I saw that the box can hold 1GB if it is programmatically added.

The code inserts the previous documentation into the new record, then any new documentation is added to that field by the user after it is inserted. I assume that the insert is considered "copy/paste" rather than programmatic?

Apologies for the delay in responding - went on holiday!

-Will