does FORMAT function cause issues with Memo fields?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003
I have a table with a memo field "branch notes" and an unbound text box on a
form that corresponds to the memo field.

I have another memo field "credit notes" associated to another text box on
the form.

I enter in lots of text into the branch notes field (> 255 chars) and add
text to the credit notes field (> 255 chars) on the form and save. When I
reopen the form the branch notes field is truncated at <255 characters and
the credit notes field is not.

The only difference I have found is that I used the format function on the
branch notes before saving to the database. When I remove the format function
then it saves more than 255 characters.

Here is the code I had before I removed the format function:
rst![BranchNotes] = Format(Me.txt_BranchNotes.Value, ">")
rst![BranchCreditNotes] = Me.txt_CreditNotes.Value

Does anyone know why this happens? Is format only intended to be used on
text fields? Is there another way to get format on memo fields?
 
Truncation of Memo fields: http://allenbrowne.com/ser-63.html

Krista said:
Access 2003
I have a table with a memo field "branch notes" and an unbound text box on a
form that corresponds to the memo field.

I have another memo field "credit notes" associated to another text box on
the form.

I enter in lots of text into the branch notes field (> 255 chars) and add
text to the credit notes field (> 255 chars) on the form and save. When I
reopen the form the branch notes field is truncated at <255 characters and
the credit notes field is not.

The only difference I have found is that I used the format function on the
branch notes before saving to the database. When I remove the format function
then it saves more than 255 characters.

Here is the code I had before I removed the format function:
rst![BranchNotes] = Format(Me.txt_BranchNotes.Value, ">")
rst![BranchCreditNotes] = Me.txt_CreditNotes.Value

Does anyone know why this happens? Is format only intended to be used on
text fields? Is there another way to get format on memo fields?
Click to expand...
 
Is format only intended to be used on text fields?
Click to expand...

Yes. Since a Memo field could be almost 2GByte in size, there are lots of
things Access won't let you do with it: sort it, group by it, format it.
Attempting to do any of these truncates it.
Is there another way to get format on memo fields?
Click to expand...

Write VBA code to read it into a String variable, modify that String, and
write it back out.

Why on Earth would you want a Memo field in all caps!?

John W. Vinson [MVP]
 
Thanks you. I think it was an accident, I had made several other text fields
all caps (Fname, LName, Address, etc) and I think I accidentally did it to
this one. I removed it because it was not needed but was curious why it was
acting this way.

:) Have a great day!
 
Back
Top