insert memo feild into a memo field truncates at 255 characters -

Greetings I have a table that has a memo field to retain specific information 
- text - which is the base of the combo box.

Via a combobox on a form this memo field is added to a forms field which is 
also sized as a memo called "Project_Notes" - this part works.

At a certain time the user uploads this form and its feilds to a parent 
table which also has a memo field to recieve the data from the other memo 
field.

'we have created the new entry now we should update the notes
                strOtherFields = ",Action_By,To_Do_date" _
                                    & ",[Project_Notes],Status"
            
                strsql = "INSERT INTO [tblsubProjectNotes] " _
                    & "(Works_Number" & strOtherFields & ") " _
                    & "SELECT '" & rst1!Works_Number & "' As 
NewWorks_Number" _
                    & strOtherFields & " FROM tblProjectNotes_Input " _
                    & "WHERE Works_Number='" & rst1!Works_Number & "'" _
                    & "AND tblProjectNotes_Input!Sent_Input = False;"

the memo field is greater than 255 characters.
On INSERT the memo field is truncated to 255 characters.

Is there a way to get around this or resolve it ???????????
Thanx in advance.

Best Regards
0
Utf
12/3/2009 3:36:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
329 Views

Similar Articles

[PageSpeed] 33

Assuming that the Works_Number field is the memo field? What is the SQL 
statement for the rst1 recordset?

-- 

        Ken Snell
http://www.accessmvp.com/KDSnell/



"Billp" <Billp@discussions.microsoft.com> wrote in message 
news:E0D9D941-8EA5-49D6-B145-516542654A72@microsoft.com...
> Greetings I have a table that has a memo field to retain specific 
> information
> - text - which is the base of the combo box.
>
> Via a combobox on a form this memo field is added to a forms field which 
> is
> also sized as a memo called "Project_Notes" - this part works.
>
> At a certain time the user uploads this form and its feilds to a parent
> table which also has a memo field to recieve the data from the other memo
> field.
>
> 'we have created the new entry now we should update the notes
>                strOtherFields = ",Action_By,To_Do_date" _
>                                    & ",[Project_Notes],Status"
>
>                strsql = "INSERT INTO [tblsubProjectNotes] " _
>                    & "(Works_Number" & strOtherFields & ") " _
>                    & "SELECT '" & rst1!Works_Number & "' As
> NewWorks_Number" _
>                    & strOtherFields & " FROM tblProjectNotes_Input " _
>                    & "WHERE Works_Number='" & rst1!Works_Number & "'" _
>                    & "AND tblProjectNotes_Input!Sent_Input = False;"
>
> the memo field is greater than 255 characters.
> On INSERT the memo field is truncated to 255 characters.
>
> Is there a way to get around this or resolve it ???????????
> Thanx in advance.
>
> Best Regards 


0
Ken
12/3/2009 3:49:07 AM
arrrrrrrrrr - thoughts here.
Project_Notes is the Memo field.
After some pain I cut the memo field up into blocks of around 255 characters 
and insert step by step.
Sorry for the delay in posting a response  just returned to home base after 
being at the branch where I asked the question.

"Ken Snell" wrote:

> Assuming that the Works_Number field is the memo field? What is the SQL 
> statement for the rst1 recordset?
> 
> -- 
> 
>         Ken Snell
> http://www.accessmvp.com/KDSnell/
> 
> 
> 
> "Billp" <Billp@discussions.microsoft.com> wrote in message 
> news:E0D9D941-8EA5-49D6-B145-516542654A72@microsoft.com...
> > Greetings I have a table that has a memo field to retain specific 
> > information
> > - text - which is the base of the combo box.
> >
> > Via a combobox on a form this memo field is added to a forms field which 
> > is
> > also sized as a memo called "Project_Notes" - this part works.
> >
> > At a certain time the user uploads this form and its feilds to a parent
> > table which also has a memo field to recieve the data from the other memo
> > field.
> >
> > 'we have created the new entry now we should update the notes
> >                strOtherFields = ",Action_By,To_Do_date" _
> >                                    & ",[Project_Notes],Status"
> >
> >                strsql = "INSERT INTO [tblsubProjectNotes] " _
> >                    & "(Works_Number" & strOtherFields & ") " _
> >                    & "SELECT '" & rst1!Works_Number & "' As
> > NewWorks_Number" _
> >                    & strOtherFields & " FROM tblProjectNotes_Input " _
> >                    & "WHERE Works_Number='" & rst1!Works_Number & "'" _
> >                    & "AND tblProjectNotes_Input!Sent_Input = False;"
> >
> > the memo field is greater than 255 characters.
> > On INSERT the memo field is truncated to 255 characters.
> >
> > Is there a way to get around this or resolve it ???????????
> > Thanx in advance.
> >
> > Best Regards 
> 
> 
> .
> 
0
Utf
12/9/2009 1:38:01 AM
I don't even see a Project_Notes field in the code that you posted. Makes it 
difficult to try to debug with you without having information... but glad 
you found a workaround. If you'd give us much more details about your code 
and query, we may be able to help you get back to where you wanted to be, 
without the workaround.
-- 

        Ken Snell
http://www.accessmvp.com/KDSnell/



"Billp" <Billp@discussions.microsoft.com> wrote in message 
news:3D5A1C64-7715-4665-BC70-E6F3B62E009D@microsoft.com...
> arrrrrrrrrr - thoughts here.
> Project_Notes is the Memo field.
> After some pain I cut the memo field up into blocks of around 255 
> characters
> and insert step by step.
> Sorry for the delay in posting a response  just returned to home base 
> after
> being at the branch where I asked the question.
>
> "Ken Snell" wrote:
>
>> Assuming that the Works_Number field is the memo field? What is the SQL
>> statement for the rst1 recordset?
>>
>> -- 
>>
>>         Ken Snell
>> http://www.accessmvp.com/KDSnell/
>>
>>
>>
>> "Billp" <Billp@discussions.microsoft.com> wrote in message
>> news:E0D9D941-8EA5-49D6-B145-516542654A72@microsoft.com...
>> > Greetings I have a table that has a memo field to retain specific
>> > information
>> > - text - which is the base of the combo box.
>> >
>> > Via a combobox on a form this memo field is added to a forms field 
>> > which
>> > is
>> > also sized as a memo called "Project_Notes" - this part works.
>> >
>> > At a certain time the user uploads this form and its feilds to a parent
>> > table which also has a memo field to recieve the data from the other 
>> > memo
>> > field.
>> >
>> > 'we have created the new entry now we should update the notes
>> >                strOtherFields = ",Action_By,To_Do_date" _
>> >                                    & ",[Project_Notes],Status"
>> >
>> >                strsql = "INSERT INTO [tblsubProjectNotes] " _
>> >                    & "(Works_Number" & strOtherFields & ") " _
>> >                    & "SELECT '" & rst1!Works_Number & "' As
>> > NewWorks_Number" _
>> >                    & strOtherFields & " FROM tblProjectNotes_Input " _
>> >                    & "WHERE Works_Number='" & rst1!Works_Number & "'" _
>> >                    & "AND tblProjectNotes_Input!Sent_Input = False;"
>> >
>> > the memo field is greater than 255 characters.
>> > On INSERT the memo field is truncated to 255 characters.
>> >
>> > Is there a way to get around this or resolve it ???????????
>> > Thanx in advance.
>> >
>> > Best Regards
>>
>>
>> .
>> 


0
Ken
12/9/2009 3:47:35 AM
Mmmm, ok I see where you have Project_Notes field name in your string 
variable. I assume that tblProjectNotes_Input is a table, not a query?

See Allen Browne's website for detailed information about truncation of memo 
fields:
http://allenbrowne.com/ser-63.html

This URL shows two posts from Jamie Collins where he tested 7 different 
scenarios for possible memo trunction:
http://groups.google.com/group/microsoft.public.access.queries/browse_thread/thread/90d4e2d1b65a60e7/489970072eafe7a3?hl=en&lnk=st&q

-- 

        Ken Snell
http://www.accessmvp.com/KDSnell/



"Ken Snell" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:%23L2CTJIeKHA.2164@TK2MSFTNGP02.phx.gbl...
>I don't even see a Project_Notes field in the code that you posted. Makes 
>it difficult to try to debug with you without having information... but 
>glad you found a workaround. If you'd give us much more details about your 
>code and query, we may be able to help you get back to where you wanted to 
>be, without the workaround.
> -- 
>
>        Ken Snell
> http://www.accessmvp.com/KDSnell/
>
>
>
> "Billp" <Billp@discussions.microsoft.com> wrote in message 
> news:3D5A1C64-7715-4665-BC70-E6F3B62E009D@microsoft.com...
>> arrrrrrrrrr - thoughts here.
>> Project_Notes is the Memo field.
>> After some pain I cut the memo field up into blocks of around 255 
>> characters
>> and insert step by step.
>> Sorry for the delay in posting a response  just returned to home base 
>> after
>> being at the branch where I asked the question.
>>
>> "Ken Snell" wrote:
>>
>>> Assuming that the Works_Number field is the memo field? What is the SQL
>>> statement for the rst1 recordset?
>>>
>>> -- 
>>>
>>>         Ken Snell
>>> http://www.accessmvp.com/KDSnell/
>>>
>>>
>>>
>>> "Billp" <Billp@discussions.microsoft.com> wrote in message
>>> news:E0D9D941-8EA5-49D6-B145-516542654A72@microsoft.com...
>>> > Greetings I have a table that has a memo field to retain specific
>>> > information
>>> > - text - which is the base of the combo box.
>>> >
>>> > Via a combobox on a form this memo field is added to a forms field 
>>> > which
>>> > is
>>> > also sized as a memo called "Project_Notes" - this part works.
>>> >
>>> > At a certain time the user uploads this form and its feilds to a 
>>> > parent
>>> > table which also has a memo field to recieve the data from the other 
>>> > memo
>>> > field.
>>> >
>>> > 'we have created the new entry now we should update the notes
>>> >                strOtherFields = ",Action_By,To_Do_date" _
>>> >                                    & ",[Project_Notes],Status"
>>> >
>>> >                strsql = "INSERT INTO [tblsubProjectNotes] " _
>>> >                    & "(Works_Number" & strOtherFields & ") " _
>>> >                    & "SELECT '" & rst1!Works_Number & "' As
>>> > NewWorks_Number" _
>>> >                    & strOtherFields & " FROM tblProjectNotes_Input " _
>>> >                    & "WHERE Works_Number='" & rst1!Works_Number & "'" 
>>> > _
>>> >                    & "AND tblProjectNotes_Input!Sent_Input = False;"
>>> >
>>> > the memo field is greater than 255 characters.
>>> > On INSERT the memo field is truncated to 255 characters.
>>> >
>>> > Is there a way to get around this or resolve it ???????????
>>> > Thanx in advance.
>>> >
>>> > Best Regards
>>>
>>>
>>> .
>>>
>
> 


0
Ken
12/9/2009 3:58:35 AM
Reply:

Similar Artilces:

How do I use the data in a named field to select a worksheet tab .
I need to use a named field to select a worksheet tab in another workbook. ie i have a workbook called Master and need to collect data from a workbook called Reports, but i need to collect it from different worksheets within Reports depending on the month selected in Master. The tabs in Reports are M01,M02,etc. In Master I have a Named Field called 'Month" with M01,M02 etc entered in it. This field needs to select the corresponding tab in Reports. Usually the reference syntax goes something like '[Filename]Tabname!Cellname or reference. Here is an obscure example from an old...

removing blank fields in a query
I need to build a table using a old database. the field I want has some data in them but can also have blanks. I do not wan the fields with blanks how do I accomplish this. -- Les >>I do not wan the fields with blanks Just omit that field. Or do you not want records that have blank fields? Use criteria of Is Not Null on the field. -- KARL DEWEY Build a little - Test a little "Les" wrote: > I need to build a table using a old database. the field I want has some data > in them but can also have blanks. I do not wan the fields with blanks how ...

Using onchange event to Hide a field
I would like to dynamically show / hide certain fields on my CRM form based upon the user's selection from a Picklist. Is there any way to dynamically show / hide a field on a form using the onchange event of the Picklist? There is no supported way to do this. You could try setting the visibility of the associated DIV to false, but no guarantees. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Tue, 16 Aug 2005 23:50:12 -0700, "cdobkins" <cdobkins@discussions.microsoft.com> wrote: I would like to dyn...

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Problem while converting excel to csv for Special Characters
Hi All, I need to convert excel sheet data into csv format data=20 file, but while doing so for some Eastern countries, like=20 Poland, special characters in the excel sheet are getting=20 converted into junk data e.g Excel Sheet Original line - Zesp=F3l Opieki Zdrowotnej w=20 Bedzinie Line when converted to CSV - Zesp=F3? Opieki Zdrowotnej w B? dzinie Really appreciate your response on this. Thanks, Pramod I'm not sure if this will help, but I just copied your first original line into a excel worksheet (USA xl2002) and saved it as .csv. I re-imported and it looked fine. I opened t...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

Showing a date field as a number
Hi. I was wondering if there was any way that I could have a column showing the date and time (20/12/2007 06:00:00) and another column show the date and time as a number, (29548). This would be done in a select query, but I am not sure how? Is it possible? Regards AJ AJ, Use format... mm/dd/yy hh:nn:ss for 20/12/2007 06:00:00 Use format... #.0000 (with Decimal Places = to suit your needs) for 39436.2500 Use format... # (with Decimal Places = 0) for 39436 -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccs...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

Variable field names?
Hi I am trying to execute the following code: - strSQL = strSQL & Nz([" & strCOM_Year & "], 0) where strCOM_Year is a string I want the system to use as a field name. It doesn't like this. Keeps saying 'can't find the field "|" referred to in you expression', even though I can clearly see in the debug window that the current value of strCOM_Year is "2006/2007". i.e. I am looking for the value in the field called 2006/2007. Any ideas? Stapes Stapes wrote: > Hi > > I am trying to execute the following code: - > > str...

Font in drop-down field too small
I created a drop-down field with multiple menu choices. I am not able to increase the font size for those menu choices. The adjacent cells have a much larger font, and I would like my spreadsheet to be consistent. If these are data validation dropdown lists, you can't change the font size. There are a couple of workarounds here: http://www.contextures.com/xlDataVal08.html#Larger Juana Cafe wrote: > I created a drop-down field with multiple menu choices. I am not able to > increase the font size for those menu choices. The adjacent cells have a > much larger font, and...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Calc Field to list invoices paid by same check on one line of repo
Hi I have a customer who wanted a check dump file to send to their bank. Once the bank receives the file (in txt or tab delimited), they will use the infomration to actually print the checks for the customer. (This is not a positive pay file situation.) I was able to take one of the check forms and modify it to get all the data required by the bank. I am running into a snafu with getting it down to one line per check with multiple invoices listed one one line. It is listing the total check and one line per each invoice paid by the check: Example: Ck 2345 for $800.00 -...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Global Customer custom data field
I am running 2 stores with HQ and Global customers. How can I change the global customer title custom fields in the Additional tab in HQ. I change the title fields in store manager, by going to Captions button under the configuration section, but HQ Manager doesn't have a captions button. How can I update those custom fields in HQ with thier current customers? Thanks Dave These values are kept in records 1-15 of the CustomCaption table of the HQ database. Using HQAdministrator, you could execute a query like: update CustomCaption set caption='my custom text' where id=1 ...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

Error when adding a custom field through Deployment Manager
I am receiving an error when trying to add a custom field through the Deployment Manager: "An error occurred during the addition of the new field. The addition failed. For more information, see the event log". The Application event log lists the following entry: "dmLog: Failed to grant access to the regenerated view Account after inserting new attribute CFSTest." My domain login has domain admin privelages and is a member of the System Administrator role in CRM. Any help with this would be greatly appreciated! this usually means the field is already there... if you c...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

Case (in)sensitivity of table names/field names
Hi ! I would like to work with Microsoft Access 2003 and Windows XP or Windows Vista. Are table names case insensitive? What about field names? Thank you very much for your kind help. Peter Yes, both are case insensitive, as is data. IOW, a search for either peter or Peter would find you. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "peschrader" <peschrader@hREMOVEMEotmail.com> wrote in message news:up9WEFmeKHA.5136@TK2MSFTNGP02.phx.gbl... > Hi ! > > I would like to work with M...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

DAO recordset on memo type field >255 long
Hi I have a table with a memo field with data frequently > 255 characters. My ultimate goal is to copy data from that table into a word document table. The method I am using is to query the table using a DAO recordset and then I loop through the record in the record set and insert them in my word table using vba code. When the data is > 255 characters long, the characters after 255 are not displayed properly in the recordset and therefore in the word document. Any idea what can be done about this? Maybe a different method for accessing the data? Thanks ...

Garbled characters when opening emails sent as attachments
On exchange 2003 SP2. Got a weird problem. The user is unable to view emails that are sent as attachments. He can not view them using OWA nor can he view them using the outlook client. If he opens and email attachment it eithers opens up with no text in it or opens with no readable characters. I emailed the user 3 emails with attachments that were in emails, in html, rich text and plain text format. They were all received. This did not replicate the problem. A colleague in his department forwarded them on and he could open them. Both the forwarder and the client are on outlook 2003....

Insert dataset to another database
I'm posting this to this group also since it pertains to queries, primarily. Hello, Using VB6/ADO, I'm thinking I can create a recordset and insert it into another table in a different Jet database, all within the execution of a single query. But, I can't seem to get it to work, even in an experiment in Access 2K. My sql in Access 2K: SELECT D.lorder as Ord, A.Lorder FROM [;Database = C:\MyDocuments\Acc2K\Wrk.mdb].OrdersData as A INNER JOIN [;Database = C:\Access\Work\Sales06.mdb].Detl1 as D On D.Lorder = a.lorder WHERE ((D.fg)= 'MXX-NC' Or (D.fg)= 'MXX.NC')...