Handling more than 255 characters of text ?

[HealdsburgCA]

Greetings all,
I’m taking a filing index of City Council Resolutions which is currently based in Excel and transferring the index to an Access database I am building. An issue I am having is that the Resolutions being indexed have a “subject” description that ranges from about 100 characters to +700. The new Access database must hold the complete contents of the Excel spreadsheet.

The direction I have taken is to divide up the subject field into three text fields as needed ([Subject1], [Subject2], [Subject3]) each with up to 255 characters. In reports I will simply combine these fields into one large text box so the user will see them as one large subject description. Memo fields are not an option because in the near future the database backend will be moved to SQL and I understand that SQL does not always play well with memo fields.

Is there something I am missing or is this the best method to achieve my goal for having the database contain the complete contents of the Resolution subject”?

Thank you in advance,

Joshua

 

[YNWA]

Re: Handleing more than 255 characters of text ?

Greetings all,
I’m taking a filing index of City Council Resolutions which is currently based in Excel and transferring the index to an Access database I am building. An issue I am having is that the Resolutions being indexed have a “subject” description that ranges from about 100 characters to +700. The new Access database must hold the complete contents of the Excel spreadsheet.

The direction I have taken is to divide up the subject field into three text fields as needed ([Subject1], [Subject2], [Subject3]) each with up to 255 characters. In reports I will simply combine these fields into one large text box so the user will see them as one large subject description. Memo fields are not an option because in the near future the database backend will be moved to SQL and I understand that SQL does not always play well with memo fields.

Is there something I am missing or is this the best method to achieve my goal for having the database contain the complete contents of the Resolution subject”?

Thank you in advance,

Joshua

Use memo, when you go onto SQL Server, it will read it. Or you can change it to a new data type such as text, char, varchar etc...

 

[HealdsburgCA]

Re: Handleing more than 255 characters of text ?

Use memo, when you go onto SQL Server, it will read it. Or you can change it to a new data type such as text, char, varchar etc...

Thank you for the reply, I understand that a common issue with memo fields is that with certain functions Access will truncate the field to the first 255 characters, will I encounter this if I use an Access front end with varchar SQL as a back end?

-Joshua

 

[pr2-eugin]

Re: Handleing more than 255 characters of text ?

Certain functions that return a String will truncate the value.. Say if you have a Memo field that has a value of char length of 564.. If you use Split, Replace, ReplaceAll, Left, Right... All these functions return a String Value as a Result..

As you may have know the max length of a String Variable is 255.. That is when the truncation happens.. Which means only the first 255 characters are returned..

If you do not use these functions Access will directly return all chars in the Memo field.. Even in future if you move to SQL you have nothing to fear, as SQL has Data types called as BLOB, TEXT.. which can hold as long as 65,535 characters..

Hope this clears up some worries you have..

 

[gemma-the-husky]

Re: Handleing more than 255 characters of text ?

generally speaking, there is not a lot of merit in indexing a long text field, i would have thought. effectively the first few characters will probably be pretty unique anyway.

worth thinking carefully about the naming system that is generating these long strings.

 

[jdraw]

There is a technique by Allen Browne that prevents chopping/truncating at 255 chars under certain condition,
you may be able to use this....
see First vs Group By at
http://allenbrowne.com/QueryPerfIssue.html