Text, Memo field in table TRUNCATES after about 500 characters

[darinmc]

I have a text box, unbound which i use the insert statement to get info into the table.

I had a problem when clicking on the list box and the memo field was TRUNCATED to about 255 characters, that was fixed by using Dlookup which now works.

HOWEVER, the insert and update statement only appear to be placing about +-500 characters ( and ends like this det????)

DoCmd.SetWarnings False
Dim sqlStr As String

sqlStr = " INSERT INTO tblBackUpInvoicing(TransID,UploadNo,Invs,WeDt,CrnNo,Crn,Faxed,FaxDt,FollowUp,Uploaded,UploadDt,ClientNo,ClientJob,ContactTo,DescripShort,DescripLong) VALUES (tTransID,tUploadNo,tInvNo,tWE,tCrnNo,chkCRN,chkFax,tFaxDt,chkFollowUp,chkUploaded,tUploadDt,tClient,tJob,cmbNameTo,tShortDesc,tDescrip)"
DoCmd.RunSQL sqlStr
Me.lstInvoicing.Requery
'Me.lstInvoicing.Selected(10) = True
DoCmd.SetWarnings True

Can you please help???
If anyone knows where i could get some good info on setting up an unbound object frame, would be greatly appreciated as well..

Thx
Darin

 

[ProgramError]

From access help files

A Text field can store up to 255 characters, but the default field size is 50 characters. The FieldSize property controls the maximum number of characters that can be entered in a Text field.

Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.


Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[darinmc]

Hi
yes, the table is set as a memo field (DescripLong). I am not using form.recordsource, instead a tab control and on that, Command buttons to Insert, Update, and Delete.

The dlookup takes all the info from table,memo field and puts it into a txt box ok.
PROBLEM is when using insert or update, it only puts around 300 - 400 characters...
The memo field should do the job, I suppose if i could get OLE to work, it might be a better option!!

I have tried a few times of setting up an OLE object but always seem to get errors, could you point me to some code or sample of doing it?


Thx
Darin

 

[ProgramError]

Have you tried checking the length of the text you are inserting into DescripLong?

use

? len([tDescrip])

in the VBA immediate window

Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[darinmc]

Hi
I have just inseted the above
? len([tDescrip])
which changed itself to
Print len([tDescrip])

When running the code, it came up as an error, in debugging, the 1st time it showed i had just over 4100 characters
BUT in fact it only inserted around 516 and then it places 2 little boxes/squares at the end
????? - this was in the table data....
Darin

 

[ProgramError]

Hi Darin,

yes the ? should change to print, its a shorthand way of typing it.
The boxes usually indicate an unprintable character.
Where are you getting the data for the tDescrip field (copying and pasting perhaps? If so, you may be including hidden characters in the original data.
The 4100 characters indication may be correct and the boxes may be the cause of you not being able to see the rest of the text. As a test try pasting it in a notepad document as this only deals with ascii text and not formatting ie colours, fonts etc.


Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[darinmc]

When I did the insert and got 4100 characters, the table itself had the squares. so when I used the dlookup it brought the squares as well..

the tDescrip field, I have tried both ways, copy and paste into that box as well as just typing rubish in quickly.

I have tried copying and pasting into the table itself, dlookup brings ALL the txt BUT when updating, it does the same as insert and truncates.

I am temporarily using a form with record source and that stores the info as required BUT i would prefer the insert/update/delete or i suppose i could also use tabcontrol form and change record source when clicking on a tab if things were really urgent...

Do you know a link to OLE bjects, setting up etc, doesnt seem that straight forward?

Thx
Darin

 

[fneily]

Have you looked at:
Truncation of Memo fields

http://allenbrowne.com/ser-63.html