Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mysterious Characters (Access 2003 (2000 format))

    The text data type has a maximum length of 255 characters.
    My guess (and it is no more than a guess) is that SQL treats the concatenation of text fields as a text field too, so the concatenated text is truncated after 255 characters. But somehow (if I'm correct, it is a bug) the length information is calculated as if no truncation has occurred, so the query result shows a string of the total length, but only the first 255 characters are correct; the rest is whatever happened to be present in memory after the 255 characters.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Mysterious Characters (Access 2003 (2000 format))

    I had a problem which I found mysterious. I have actually solved the problem, but I still do not know why the problem arose, nor why the solution worked.

    I have a procedure that takes data from a db and writes it up into HTML for posting on a website. I open a recordset , then loop through writing each record into an html table.

    Here is the sql:
    sql = " SELECT [LotNo] & [LotNoLetter] AS LotNumber, tblItems.Location, " _
    & " [LotDescription] & " " & IIf([Quantity]>1,' (x' & [quantity] & ')','') & IIf(([ShowCustfield1]=True) And (Len([Customfield1])>0),', ' & [custfield1] & ': ' & [Customfield1],'') " _
    & IIf(([ShowCustfield2]=True) And (Len([customfield2])>0),', ' & [custfield2] & ': ' & [Customfield2],'') AS LotDescription2," _
    & " tblItems.Estimate, tblItems.SalePrice, tblItems.Outcome, tblItems.Photo " _
    & "FROM tblAuctions INNER JOIN tblItems ON tblAuctions.AuctionID = tblItems.AuctionID " _
    & " WHERE (((tblItems.AuctionID) =" & AuctionID & "))" _
    & " ORDER BY [LotNo] , [LotNoLetter]"

    I am creating a calculated field LotDescription2 from LotDescription and a few other fields, using some IIf functions to decide if i want the other stuff (which I usually don't) If I capture this sql and run it as a query it returns exactly what I want.

    Then later I put the value into a string

    strLotDescription = rs![LotDescription2] , then wrap this in html.

    This has worked Ok for a long time.

    I now have a user who is putting in some very long LotDescriptions (about 500 characters). In this case the first 255 char of the description appears as intended, and the rest appears as nonsense characters. see Below.

    If I change things a bit so that the concatenation does not occur in the query, but later, when I put values inot the string, it all works OK

    sql = " SELECT [LotNo] & [LotNoLetter] AS LotNumber, tblItems.Location, " _
    & " [LotDescription], IIf([Quantity]>1,' (x' & [quantity] & ')','') & IIf(([ShowCustfield1]=True) And (Len([Customfield1])>0),', ' & [custfield1] & ': ' & [Customfield1],'') " _
    & IIf(([ShowCustfield2]=True) And (Len([customfield2])>0),', ' & [custfield2] & ': ' & [Customfield2],'') AS LotDescription2," _
    & " tblItems.Estimate, tblItems.SalePrice, tblItems.Outcome, tblItems.Photo " _
    & "FROM tblAuctions INNER JOIN tblItems ON tblAuctions.AuctionID = tblItems.AuctionID " _
    & " WHERE (((tblItems.AuctionID) =" & AuctionID & "))" _
    & " ORDER BY [LotNo] , [LotNoLetter]"


    strLotDescription = rs![LotDescription] & rs![LotDescription2]

    Any suggestions as to why?
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Mysterious Characters (Access 2003 (2000 format))

    Thanks Hans

    The Lot Description is actually a Memo (that is why it can hold over 500 char). So in the sql I am concatenating a Memo with some additional text. This works OK as a query, but not in a recordset.

    I am happy to just write this off as a bug.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •