Changing Field data type (1 Viewer)

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
Hi,

I need to change a field data type in one of my tables from Short Text to Long Text. The data currently conforms to the Short Text character limit but I redefined the use of the field so it will soon require more room. Can I simply change the data type or is there something else required?

Gale
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:49
Joined
Feb 28, 2001
Messages
27,001
Go into table design view. Find that field. Change its type. Try to save it. Since you are going from "smaller" to "larger" I would expect no problems. However... be aware that LongText fields are less versatile than ShortText fields, particularly when searching or indexing. Will you actually need more than 255 characters in a text field? If so, then you have to do what you have to do.
 

moke123

AWF VIP
Local time
Today, 19:49
Joined
Jan 11, 2013
Messages
3,852
Should not be a problem going text to memo or long text. If going from long text or memo to text you may get a warning about data loss .
 

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
Thank you The_Doc_Man and moke123, that is what I hoped would be the case. Yes, I am sure I need more than 255 characters but I did not know there is a difference in versatility of the two data types... I'll need to look into that. In tis case I will not be searching for information in this field... it will be an event log.

Gale
 

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
Thank you Micron, I appreciate your comment. No, it isn't indexed and it's not part of a join. It's kind of a free form log area for manual entry of process exceptions and complications. Used primarily to detail the current state of a document if it isn't flowing smoothly through the process.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:49
Joined
Feb 19, 2013
Messages
16,553
so it will soon require more room
short or long, the room required is the broadly the length of the text (2 bytes per char + 2 bytes). Note that long text can be formatted for colour/font etc - this involve hidden characters like html code so

hello world

will take more space than

hello world
 

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
Thank you CJ_London. That makes it difficult to count characters... but the field will just truncate if the max number is exceeded... correct? Or do I have to detect that and stop the save? It's ok with me if the text is simply truncated. I really don't want to get into character counting if I can avoid it :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:49
Joined
Feb 19, 2013
Messages
16,553
long text can have 65k characters so unless your are writing a book:) I don't think you need to worry about counting characters. My post was because you seemed to be concerned that changing the datatype would require more space in the database
 

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
@ CJ_London "concerned that changing the datatype would require more space in the database "
I was mostly concerned that changing the data type might mess up the table if I didn't do it correctly. But your point is still a good one and something to keep in mind for larger databases.

@ moke123, thank you for the link... I'll read it.

Gale
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:49
Joined
Feb 28, 2001
Messages
27,001
There is another viewpoint if you would like to consider it...

Instead of having one big memo field, make that a child table that has fields like "document ID" and "person ID" (of the person making a note) and "NoteDate" showing when the note was made and "TheNote" up to 255 characters. Then instead of making one humongous note field, you have a nearly unlimited number of slightly smaller note fields. As long as any single note is shorter than 255 characters, you can take all sorts of notes - and organize them by date or by author. If the notes are then related to the document record by Document ID (or something like that) then you have a history that you can still organize, search, and use as the basis for a document history.
 

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
Very interesting the_Doc_Man... thank you. In this case it would be overkill but the method is good to know and I appreciate the information.

Gale
 

Micron

AWF VIP
Local time
Today, 19:49
Joined
Oct 20, 2018
Messages
3,476
In this case it would be overkill
If you are concatenating notes/comments/whatever that are comprised over a period of time or over different users, it's not overkill, it's the right way to do it. If I've described your case, I see a post in your future about how to deal with the data.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:49
Joined
Jan 20, 2009
Messages
12,849
Used primarily to detail the current state of a document if it isn't flowing smoothly through the process.

Are you extending the text in the field as the process progresses?

If so you should rethink this. Much better strategy to bring out the data into a related table with each new comment added as a separate record in a text field.
 

GaleT

Registered User.
Local time
Today, 16:49
Joined
Oct 18, 2019
Messages
72
If you are concatenating notes/comments/whatever that are comprised over a period of time or over different users, it's not overkill, it's the right way to do it. If I've described your case, I see a post in your future about how to deal with the data.

Thank you, I will keep this in mind as the future of this database is decided. I am not sure how it will be used or even if it will be used. I am replacing a database that I developed in Lotus Notes and have used personally for over 10 years. But want to give the work this database supports to someone else. If that turns out to be a group I will have to reconsider how I am handling the comments. But for now... it's just me and I need to concentrate on getting ten years worth of data in shape :)

Gale
 

moke123

AWF VIP
Local time
Today, 19:49
Joined
Jan 11, 2013
Messages
3,852
Wasn't that fixed in a later version of Access? I thought I recall, but I'm not sure.
Not sure to be honest but I do recall the question popping up from time to time, much like the corruption issue.
It is microsoft so . . .
 

Micron

AWF VIP
Local time
Today, 19:49
Joined
Oct 20, 2018
Messages
3,476
Wasn't that fixed in a later version of Access? I thought I recall, but I'm not sure.
Maybe some of the reasons from days of yore are no longer but some still are from what I've read. One would be using DISTINCT predicate in a query. Another might be GROUP BY.
 

Users who are viewing this thread

Top Bottom