Short Text limited to 50 characters? (1 Viewer)

[pajz]

Hi, I'm using Access (Microsoft 365) for the first time today. I created a field of the type Long Text, then added some entries, and ultimately decided to change the field type to Short Text. My thought was, based on a quick Google search, that since Short Text is limited to 255 characters, and my entries in the field were all well below 255 characters, I wouldn't have to worry about length issues. However, after changing the field data type (and ignoring Access's warning message ...), I noticed that my entries in the field were now all cut off after 50 characters and no additional characters could be added. Where is this limitation to 50 characters coming from? Can it be changed or what am I missing? Thanks!

(Options -> Object Designers -> Default Text field type is set to Short Text and Default text field size is set to 255, in case that's relevant.)

 

[GPGeorge]

Hi, I'm using Access (Microsoft 365) for the first time today. I created a field of the type Long Text, then added some entries, and ultimately decided to change the field type to Short Text. My thought was, based on a quick Google search, that since Short Text is limited to 255 characters, and my entries in the field were all well below 255 characters, I wouldn't have to worry about length issues. However, after changing the field data type (and ignoring Access's warning message ...), I noticed that my entries in the field were now all cut off after 50 characters and no additional characters could be added. Where is this limitation to 50 characters coming from? Can it be changed or what am I missing? Thanks!

(Options -> Object Designers -> Default Text field type is set to Short Text and Default text field size is set to 255, in case that's relevant.)

Show us the table in design view, with that field highlighted.

 

[theDBguy]

Hi @pajz. Welcome to AWF! As @GPGeorge said, the answer is in the design view of your table.

 

[pajz]

Here it is. Hm, I can see it says field size: 50. (But why? Can I influence this default(?) size?)

 

[theDBguy]

Here it is. Hm, I can see it says field size: 50. (But why? Can I influence this default(?) size?)

You can manually change the size from 50 to 255. When you add a new Short Text field, what size does it automatically assign?

 

[pajz]

You can manually change the size from 50 to 255. When you add a new Short Text field, what size does it automatically assign?

255. And you're right, I can manually change it now from 50 to 255, meaning I can restore my old entries. Thank you!

I'm curious if there's a way to change the "conversion default" from 50 to a higher value to prevent losing data in the first place.

 

[CJ_London]

if you got to File>Options>Object Designers, the default is set there

 

[pajz]

if you got to File>Options>Object Designers, the default is set there

Thanks, but this value has been set to 255 here all along. And indeed, when I create a new Short Text field, its field size is 255. My issue was/is that when I converted a Long Text field to Short Text, it apparently did not use this 255 characters default, trimming my ~100 characters entries to 50 characters.

 

[ebs17]

I created a field of the type Long Text, then added some entries, and ultimately decided to change the field type to Short Text.

This is also a sledgehammer method.
It could also occur to you to first explicitly shorten the contents before you change the data type.

UPDATE TableX SET MyField = Left(MyField, 255)

 

[SHANEMAC51]

Hi, I'm using Access (Microsoft 365) for the first time today. I created a field of the type Long Text, then added some entries, and ultimately decided to change the field type to Short Text. My thought was, based on a quick Google search, that since Short Text is limited to 255 characters, and my entries in the field were all well below 255 characters, I wouldn't have to worry about length issues. However, after changing the field data type (and ignoring Access's warning message ...), I noticed that my entries in the field were now all cut off after 50 characters and no additional characters could be added. Where is this limitation to 50 characters coming from? Can it be changed or what am I missing? Thanks!

(Options -> Object Designers -> Default Text field type is set to Short Text and Default text field size is set to 255, in case that's relevant.)

parametr ms access

 

[Pat Hartman]

Welcome

Thanks, but this value has been set to 255 here all along. And indeed, when I create a new Short Text field, its field size is 255. My issue was/is that when I converted a Long Text field to Short Text, it apparently did not use this 255 characters default, trimming my ~100 characters entries to 50 characters.

The old default for short text was 255 but newer versions of Access default to 50. Sounds like the conversion code is using an internal hard-coded value of 50 rather than using your defined default value of 255. MS will tell you that this is not a bug, it is a "feature" so you should report it, just in case because I believe you are right, the conversion should use the default YOU defined.

The safest way to convert a data type with existing data is to:
-remove any existing relationships
-rename the old column
-add a new column with the new definition and the old name
-create an update query that copies the data from the old column to the new column.
-validate the data. If you have a lot of it, you can try running a query with criteria of Where oldcolumn <> newcolumn to see if there are any differences.
-once you are happy with the results, delete the old column
-c&r the database
-reset any relationships you removed earlier

Final words to the wise:
Do NOT blow by error messages unless you actually understand what they are telling you

 

[ebs17]

remove any existing relationships
reset any relationships you removed earlier

Why such, if the field has nothing to do with relationships at all? Memo / LongText?

The "safest way" quickly turns into a source of additional errors to commit.

 

[Pat Hartman]

Why such, if the field has nothing to do with relationships at all? Memo / LongText?

That is THIS situation. I was defining a general procedure for any type of data type change.

The "safest way" quickly turns into a source of additional errors to commit.

What are you talking about?

 

[gemma-the-husky]

I think the default size of short text was 50, (A97, maybe A2003), but it changed with maybe A2013. It seems to have returned to 50 again.

 

[ebs17]

I was defining a general procedure for any type of data type change.

Interventions on keys in relations would not be simple data type changes for me, but run under the heading change of the database schema and are another topic.
At least keys of two tables are then affected, which should be reflected as a fact in a general comprehensive description of the todo.

 

[moke123]

In my O365 short text defaults to 255. What bothers me is number datatypes default to 0. I don't recall it being that way in A2010. I don't see anywhere to change that.

 

[Pat Hartman]

Interventions on keys in relations would not be simple data type changes for me, but run under the heading change of the database schema and are another topic.

You've probably never made the mistake of trying to make a FK on text to text rather than on Integer to autonumber. The response wasn't written for "you", it was written for a beginner who might have made that mistake and now needs to correct it.

 

[ebs17]

If you look at it from that perspective, you're right, of course. Everything that is somehow possible is also done. Not only by users of an application, but also by developers.
Where there is a puddle, many step into it. Where there is a precipice, many want to know the boundary where one begins to fall.

In this sense, one would have to extend a step significantly:

create an update query that copies the data from the old column to the new column

In copying, active conversion of the content would have to take place.

 

[Pat Hartman]

In copying, active conversion of the content would have to take place.

Yes it would but that was far beyond the scope of the answer. If anyone makes the mistake I mentioned, we provide detailed instructions on how to convert the text field to the correct FK value.

 

[arnelgp]

The old default for short text was 255 but newer versions of Access default to 50.

it is still 255.