Results 1 to 8 of 8
  1. #1
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61

    Seems like my strings are getting trimmed to 255 chars???

    I have a procedure in one of my modules in which I Dim strBodyText As String and assign it long string values generated in a query. Those strings are acting mighty funny. First of all, they have embedded CRLF's that seem to be fine in the query output. Cutting and pasting one of the query fields into Notepad leaves all the paragraphs as expected. But assigning the same field's value to a string var in VBA and passing that string var's value to a FN that pastes the string into the BodyText section of an Outlook eMail - well, the paragraphs run together as if no CRLF's are being recognized. Secondly, the strings seem to consist of about 255 recognizable chars at the beginning, then terminate in a bunch of hieroglyphics & Japanese Kanji characters. Not good. Solutions anyone?



    Thx.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe something in THIS LINK will shed some light on the subject.

  3. #3
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    I was sure hoping it would help. But it did not. All I'm doing with the query is opening it and traversing its dynaset through DAO, assigning field values to string var's and using Debug.Print to reflect them in the immediate window. The garbage at the end of the 245-255 chars (or thereabout) persists - even when printed to the immediate window. I first noticed the garbage when putting the text values into the body text of outlook emails. I thought the problem might have been in the transition of simple data in Access to data in the bodytext field of an email. But no. The garbage is present when the string var is printed to the debug window.

    Crap!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about posting the SQL for the query?

  5. #5
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Quote Originally Posted by RuralGuy View Post
    Hoe about posting the SQL for the query?
    Doing the T'giving thing right about now. Will do when I get back to the office. Wish I'd thought-a-that B4 leaving last night. Worthy of mention is the fact that the long strings work PERFECTLY when the query is merely used for report food. But when opened & traversed via DAO, and field values (long strings) are assigned to strVars (dim'd as strings) in the procedure, they get trunc'd (and distorted) regardless of whether I simply debug.print them or shove the into the BodyText of an Outlook email.

  6. #6
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Quote Originally Posted by RuralGuy View Post
    How about posting the SQL for the query?
    SELECT [tblContacts].[ContactFName] & " " & [tblContacts].[ContactLName] AS ContactFullName, [ContactFullName] & IIf(Not IsNull([tblContacts].[Title]),", " & [tblContacts].[Title],"") & Chr$(13) & Chr$(10) & [Company] AS FormattedContactName, [ContactFullName] & IIf(Not IsNull([tblContacts].[Title]),", " & [tblContacts].[Title],"") & Chr$(13) & Chr$(10) & [Company] & Chr$(13) & Chr$(10) & [tblProspects].[ProspectID] AS Cheater, [Addr] & IIf(Not IsNull([Suite]),", " & [Suite],"") AS FormattedAddr, IIf(Not IsNull([tblAlternateContactInfo].[ContactID]),[tblAlternateContactInfo].[AltAddr] & IIf(Not IsNull([tblAlternateContactInfo].[AltSuite]),", " & [tblAlternateContactInfo].[AltSuite],""),"") AS FormattedAltAddr, [FormattedContactName] & Chr$(13) & Chr$(10) & IIf(Not IsNull([tblAlternateContactInfo].[ContactID]),[FormattedAltAddr],[FormattedAddr]) & IIf(Not IsNull([tblAlternateContactInfo].[ContactID]),Chr$(13) & Chr$(10) & [AltCity] & ", " & [AltST] & " " & [AltZip],Chr$(13) & Chr$(10) & [City] & ", " & [ST] & " " & [Zip]) & Chr$(13) & Chr$(10) & Format(IIf(Not IsNull([tblContacts].[Phn]),[tblContacts].[Phn],[tblProspects].[Phn]),"(@@@) @@@-@@@@") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) AS CustNameAddr, [tblContacts].[ContactFName] & ":" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "On behalf of all of us here at Freeman Sets and Service, I want to wish you and your colleagues at " & [Company] & " a happy and safe Thanksgiving holiday." AS BodyParagraph1, Nz([NewStoresNext12mos],0)+Nz([RelocatesNext12mos],0)+Nz([RemodelsNext12mos],0) AS NewJobs, Nz([NewStoresNext12mos],0) AS NewStores, Nz([RelocatesNext12mos],0) AS Relocates, Nz([RemodelsNext12mos],0) AS Remodels, IIf([NewStores]>0,1,0)+IIf([Relocates]>0,2,0)+IIf([Remodels]>0,4,0) AS SumBits, IIf([SumBits]=0,"") & IIf([SumBits]=1,"It's a 1.","") & IIf([SumBits]=2,"It's a 2.","") & IIf([SumBits]=3,"It's a 3.","") & IIf([SumBits]=4,"It's a 4.","") & IIf([SumBits]=5,IIf([NewStores]>1,Trim$(CStr([NewStores])) & " relocation jobs and ","one relocation job and ") & IIf([Remodels]>1,Trim$(CStr([Remodels])) & " remodeling jobs","one remodeling job") & " within the next twelve months. ","") & IIf([SumBits]=6,IIf([Relocates]>1,Trim$(CStr([Relocates])) & " relocation jobs and ","one relocation job and ") & IIf([Remodels]>1,Trim$(CStr([Remodels])) & " remodeling jobs","one remodeling job") & " within the next twelve months. ","") & IIf([SumBits]=7,IIf([NewStores]>1,Trim$(CStr([NewStores])) & " new stores, ","one new store, ") & IIf([Relocates]>1,Trim$(CStr([Relocates])) & " relocation jobs and ","one relocation job and ") & IIf([Remodels]>1,Trim$(CStr([Remodels])) & " remodeling jobs","one remodeling job") & " within the next twelve months. ","") AS PP2Sentence1, "" AS BodyParagraph2, "" AS BodyParagraph3, "" AS BodyParagraph4, Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & [tblContacts].[ContactFName] & ", I look forward to the time when our company can be of service to yours. Feel free to call me toll free at (888) 769-9672. Or, email me at your leisure: mlh@FreemanSets.com with the best time to call." AS BodyParagraph5, Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Sincerely," & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Michael L Harvell, Director of Marketing" & Chr$(13) & Chr$(10) & "Freeman Companies - High Point, NC" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "P.S. Please stop by our booth (#526) at the 47th annual SPECS conference in Gaylord, TX, March 13-16" AS Salutation, [CustNameAddr] & Format(Now(),"Long Date") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & [BodyParagraph1] & [BodyParagraph2] & [BodyParagraph3] & [BodyParagraph4] & [BodyParagraph5] & [Salutation] AS BodyText, "Happy Thanksgiving to " & [Company] & " from all of us here at Freeman Companies" AS Subject, tblProspects.Active, tblContacts.ContactID, tblContacts.eMail
    FROM (tblindustryTypes RIGHT JOIN tblProspects ON tblindustryTypes.IndTypeID = tblProspects.IndTypeID) INNER JOIN (tblContacts LEFT JOIN tblAlternateContactInfo ON tblContacts.ContactID = tblAlternateContactInfo.ContactID) ON tblProspects.ProspectID = tblContacts.ProspectID
    WHERE (((tblProspects.Active)=True) AND ((tblContacts.eMail) Is Not Null) AND ((tblProspects.Company) Is Not Null) AND ((tblProspects.City) Is Not Null) AND ((tblProspects.ST) Is Not Null) AND ((tblProspects.Zip) Is Not Null) AND ((tblContacts.ContactFName) Is Not Null) AND ((tblContacts.ContactLName) Is Not Null))
    ORDER BY tblProspects.ProspectID;

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you follow this additional link in the link I provided? http://allenbrowne.com/bug-16.html

  8. #8
    yeah is offline Advanced Beginner
    Windows XP Access 2000
    Join Date
    Nov 2010
    Posts
    61
    Quote Originally Posted by RuralGuy View Post
    Did you follow this additional link in the link I provided? http://allenbrowne.com/bug-16.html
    I'm about to do just that. Before mucking around with a UNION query, I decided to make the query an APPEND query, intended to yank out data in the 3 important fields (subject, email address & body text) and append it to a table whose body text field is a MEMO type.

    Of all the backward-assed scenarios I've ever come across! Access puked. Earlier, I thought a MAKETABLE query-type was successful in writing to the long text field. No. That query creates the field as a TEXT field - thus, the limitation there.

    Having said that, I'm having some difficulty with the UNION query methodology discussed on your site. It would present a more elegant solution w/o requiring hard disk writes of physical tabular data. I just don't know how to structure my union query from first couple of reads on your site. I tried the following (wouldn't even compile). I get a -3087 reserved error code.
    SELECT BodyText FROM StructureOnly
    UNION ALL
    SELECT [tblContacts].[ContactFName] & " " & [tblContacts].[ContactLName] AS ContactFullName, [ContactFullName] & IIf(Not IsNull([tblContacts].[Title]),", " & [tblContacts].[Title],"") & Chr$(13) & Chr$(10) & [Company] AS FormattedContactName, [ContactFullName] & IIf(Not IsNull([tblContacts].[Title]),", " & [tblContacts].[Title],"") & Chr$(13) & Chr$(10) & [Company] & Chr$(13) & Chr$(10) & [tblProspects].[ProspectID] AS Cheater, [Addr] & IIf(Not IsNull([Suite]),", " & [Suite],"") AS FormattedAddr, IIf(Not IsNull([tblAlternateContactInfo].[ContactID]),[tblAlternateContactInfo].[AltAddr] & IIf(Not IsNull([tblAlternateContactInfo].[AltSuite]),", " & [tblAlternateContactInfo].[AltSuite],""),"") AS FormattedAltAddr, [FormattedContactName] & Chr$(13) & Chr$(10) & IIf(Not IsNull([tblAlternateContactInfo].[ContactID]),[FormattedAltAddr],[FormattedAddr]) & IIf(Not IsNull([tblAlternateContactInfo].[ContactID]),Chr$(13) & Chr$(10) & [AltCity] & ", " & [AltST] & " " & [AltZip],Chr$(13) & Chr$(10) & [City] & ", " & [ST] & " " & [Zip]) & Chr$(13) & Chr$(10) & Format(IIf(Not IsNull([tblContacts].[Phn]),[tblContacts].[Phn],[tblProspects].[Phn]),"(@@@) @@@-@@@@") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) AS CustNameAddr, [tblContacts].[ContactFName] & ":" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "On behalf of all of us here at Freeman Sets and Service, I want to wish you and your colleagues at " & [Company] & " a happy and safe Thanksgiving holiday." AS BodyParagraph1, Nz([NewStoresNext12mos],0)+Nz([RelocatesNext12mos],0)+Nz([RemodelsNext12mos],0) AS NewJobs, Nz([NewStoresNext12mos],0) AS NewStores, Nz([RelocatesNext12mos],0) AS Relocates, Nz([RemodelsNext12mos],0) AS Remodels, IIf([NewStores]>0,1,0)+IIf([Relocates]>0,2,0)+IIf([Remodels]>0,4,0) AS SumBits, IIf([SumBits]=0,"") & IIf([SumBits]=1,"It's a 1.","") & IIf([SumBits]=2,"It's a 2.","") & IIf([SumBits]=3,"It's a 3.","") & IIf([SumBits]=4,"It's a 4.","") & IIf([SumBits]=5,IIf([NewStores]>1,Trim$(CStr([NewStores])) & " relocation jobs and ","one relocation job and ") & IIf([Remodels]>1,Trim$(CStr([Remodels])) & " remodeling jobs","one remodeling job") & " within the next twelve months. ","") & IIf([SumBits]=6,IIf([Relocates]>1,Trim$(CStr([Relocates])) & " relocation jobs and ","one relocation job and ") & IIf([Remodels]>1,Trim$(CStr([Remodels])) & " remodeling jobs","one remodeling job") & " within the next twelve months. ","") & IIf([SumBits]=7,IIf([NewStores]>1,Trim$(CStr([NewStores])) & " new stores, ","one new store, ") & IIf([Relocates]>1,Trim$(CStr([Relocates])) & " relocation jobs and ","one relocation job and ") & IIf([Remodels]>1,Trim$(CStr([Remodels])) & " remodeling jobs","one remodeling job") & " within the next twelve months. ","") AS PP2Sentence1, "" AS BodyParagraph2, "" AS BodyParagraph3, "" AS BodyParagraph4, Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & [tblContacts].[ContactFName] & ", I look forward to the time when our company can be of service to yours. Feel free to call me toll free at (888) 769-9672. Or, email me at your leisure: mlh@FreemanSets.com with the best time to call." AS BodyParagraph5, Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Sincerely," & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Michael L Harvell, Director of Marketing" & Chr$(13) & Chr$(10) & "Freeman Companies - High Point, NC" & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "P.S. Please stop by our booth (#526) at the 47th annual SPECS conference in Gaylord, TX, March 13-16" AS Salutation, [CustNameAddr] & Format(Now(),"Long Date") & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & [BodyParagraph1] & [BodyParagraph2] & [BodyParagraph3] & [BodyParagraph4] & [BodyParagraph5] & [Salutation] AS BodyText, "Happy Thanksgiving to " & [Company] & " from all of us here at Freeman Companies" AS Subject, tblProspects.Active, tblContacts.ContactID, tblContacts.eMail INTO tblMassMailTemporaryFood
    FROM (tblindustryTypes RIGHT JOIN tblProspects ON tblindustryTypes.IndTypeID = tblProspects.IndTypeID) INNER JOIN (tblContacts LEFT JOIN tblAlternateContactInfo ON tblContacts.ContactID = tblAlternateContactInfo.ContactID) ON tblProspects.ProspectID = tblContacts.ProspectID
    WHERE (((tblProspects.Active)=True) AND ((tblContacts.eMail) Is Not Null) AND ((tblProspects.Company) Is Not Null) AND ((tblProspects.City) Is Not Null) AND ((tblProspects.ST) Is Not Null) AND ((tblProspects.Zip) Is Not Null) AND ((tblContacts.ContactFName) Is Not Null) AND ((tblContacts.ContactLName) Is Not Null))
    ORDER BY tblProspects.ProspectID;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Complex search through strings in records
    By pkstormy in forum Code Repository
    Replies: 2
    Last Post: 03-17-2012, 11:25 AM
  2. Evaluating Math Strings
    By Rawb in forum Programming
    Replies: 18
    Last Post: 09-01-2010, 07:50 AM
  3. Grouping strings
    By Fre in forum Access
    Replies: 16
    Last Post: 04-24-2010, 03:46 PM
  4. How to force carriage return between strings?
    By Divardo in forum Reports
    Replies: 1
    Last Post: 05-21-2009, 10:50 AM
  5. Strings limited to 255 characters
    By ocordero in forum Programming
    Replies: 4
    Last Post: 08-09-2006, 09:13 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums