Query result is being truncated (1 Viewer)

[RogerCooper]

I have query that is being used to generate the body of an e-mail. Sometimes, the result should be more than 256 characters. However, when the query is run, the value is truncated. Is there a way of having a query generate a result that is more than 256 characters?

 

[CJ_London]

if the text is being created by combining fields, try making the fields memo/long text

alternatively break the string down into two (or more) columns returned by the query then combine them in your vba code to create the full body text.

 

[The_Doc_Man]

Thinking about this, I have to ask how you get the data from a query result field to a message body (technically a property). I don't recall that you can do that in one step, so the question is what is the intermediary? What is the go-between that takes your query field to the message body. The truncation probably occurs there.

However, it wouldn't hurt to verify that you do actually GET back more than 255 characters from the query. Saying it SHOULD return a longer string is fine, but DOES it actually return the longer string if you open the query directly without the e-mail attempt?

 

[Josef P.]

Just to be clear: is Group By used?

 

[RogerCooper]

if the text is being created by combining fields, try making the fields memo/long text

alternatively break the string down into two (or more) columns returned by the query then combine them in your vba code to create the full body text.

Many of the fields are from an external database that I can't change the field type for. I tried using cancatenating a long text field at the start of the field, but it did not help. Neither did changing the field type with CVAR, either for the first element of the field or the entire field.

I definitely can break up the field, but wanted to see if there was a better solution.

 

[RogerCooper]

Thinking about this, I have to ask how you get the data from a query result field to a message body (technically a property). I don't recall that you can do that in one step, so the question is what is the intermediary? What is the go-between that takes your query field to the message body. The truncation probably occurs there.

However, it wouldn't hurt to verify that you do actually GET back more than 255 characters from the query. Saying it SHOULD return a longer string is fine, but DOES it actually return the longer string if you open the query directly without the e-mail attempt?

There is an intermediary, but the truncation is visible in the query itself.

SELECT DISTINCT dbo_Receiver.ID AS Receiver_ID, dbo_RECEIVER_LINE.LINE_NO AS Line_No, dbo_Purchase_Order.ID AS PO_ID, dbo_Purchase_Order.VENDOR_ID AS Vendor_ID, dbo_Vendor.USER_5 AS [Vendor Name], dbo_Vendor.COUNTRY AS Country, dbo_PURC_ORDER_LINE.PART_ID AS Part_ID, Combined_Part.Description, "Commercial Invoice" AS [Attach-1], "Packing List" AS [Attach-2], [Part Lines].[Entry#] AS [Import Entry], dbo_Receiver.RECEIVED_DATE AS Received_Date, "Import Drawback for " & Year([Received_Date]) & " " & [Receiver_ID] & " Entry#: " & [Entry#] AS Subject, "Receiver_ID: " & [Receiver_ID] & Chr(13) & "PO#: " & [dbo_receiver]![Purc_Order_ID] & Chr(13) & "Vendor_ID: " & [Vendor_ID] & Chr(13) & "Vendor Name: " & [dbo_Vendor]![User_5] & Chr(13) & "Country: " & [Country] & Chr(13) & "Part_ID: " & [dbo_Purc_Order_Line]![Part_ID] & Chr(13) & "Description: " & [Combined_Part]![Description] & Chr(13) & "Import Entry: " & [Entry#] & Chr(13) & "Received Date: " & [Received_Date] & Chr(13) & Chr(13) & "Roger Cooper" & Chr(13) & "Spectronics Corporation" AS Body
FROM (([Import Drawback Receiver] INNER JOIN ((((dbo_Receiver INNER JOIN dbo_Purchase_Order ON dbo_Receiver.PURC_ORDER_ID = dbo_Purchase_Order.ID) INNER JOIN dbo_Vendor ON dbo_Purchase_Order.VENDOR_ID = dbo_Vendor.ID) INNER JOIN dbo_RECEIVER_LINE ON dbo_Receiver.ID = dbo_RECEIVER_LINE.RECEIVER_ID) INNER JOIN dbo_PURC_ORDER_LINE ON (dbo_RECEIVER_LINE.PURC_ORDER_LINE_NO = dbo_PURC_ORDER_LINE.LINE_NO) AND (dbo_RECEIVER_LINE.PURC_ORDER_ID = dbo_PURC_ORDER_LINE.PURC_ORDER_ID)) ON [Import Drawback Receiver].Receiver_ID = dbo_Receiver.ID) INNER JOIN Combined_Part ON dbo_PURC_ORDER_LINE.PART_ID = Combined_Part.Part_ID) LEFT JOIN [Part Lines] ON (dbo_RECEIVER_LINE.RECEIVER_ID = [Part Lines].Receiver_ID) AND (dbo_RECEIVER_LINE.PURC_ORDER_ID = [Part Lines].[PO#])
ORDER BY dbo_Receiver.ID, dbo_RECEIVER_LINE.LINE_NO;

The "Body" field is being truncated at the query level.

There is no groupby being used.

 

[The_Doc_Man]

Sometimes when you break it up for reading, it becomes easier to see what is going on.

SELECT DISTINCT dbo_Receiver.ID AS Receiver_ID,
    dbo_RECEIVER_LINE.LINE_NO AS Line_No,
    dbo_Purchase_Order.ID AS PO_ID,
    dbo_Purchase_Order.VENDOR_ID AS Vendor_ID,
    dbo_Vendor.USER_5 AS [Vendor Name],
    dbo_Vendor.COUNTRY AS Country,
    dbo_PURC_ORDER_LINE.PART_ID AS Part_ID,
    Combined_Part.Description,
    "Commercial Invoice" AS [Attach-1],
    "Packing List" AS [Attach-2],
    [Part Lines].[Entry#] AS [Import Entry],
    dbo_Receiver.RECEIVED_DATE AS Received_Date,
    "Import Drawback for " & Year([Received_Date]) & " " & [Receiver_ID] & " Entry#: " & [Entry#] AS Subject,
    "Receiver_ID: " & [Receiver_ID] & Chr(13) & "PO#: " & [dbo_receiver]![Purc_Order_ID] & Chr(13) & "Vendor_ID: " & [Vendor_ID] & Chr(13)
        & "Vendor Name: " & [dbo_Vendor]![User_5] & Chr(13) & "Country: " & [Country] & Chr(13) & "Part_ID: " & [dbo_Purc_Order_Line]![Part_ID]
        & Chr(13) & "Description: " & [Combined_Part]![Description] & Chr(13) & "Import Entry: " & [Entry#] & Chr(13) & "Received Date: "
        & [Received_Date] & Chr(13) & Chr(13) & "Roger Cooper" & Chr(13) & "Spectronics Corporation" AS Body
FROM (([Import Drawback Receiver]
    INNER JOIN ((((dbo_Receiver INNER JOIN dbo_Purchase_Order ON dbo_Receiver.PURC_ORDER_ID = dbo_Purchase_Order.ID)
    INNER JOIN dbo_Vendor ON dbo_Purchase_Order.VENDOR_ID = dbo_Vendor.ID)
    INNER JOIN dbo_RECEIVER_LINE ON dbo_Receiver.ID = dbo_RECEIVER_LINE.RECEIVER_ID)
    INNER JOIN dbo_PURC_ORDER_LINE ON (dbo_RECEIVER_LINE.PURC_ORDER_LINE_NO = dbo_PURC_ORDER_LINE.LINE_NO)
        AND (dbo_RECEIVER_LINE.PURC_ORDER_ID = dbo_PURC_ORDER_LINE.PURC_ORDER_ID))
    ON [Import Drawback Receiver].Receiver_ID = dbo_Receiver.ID)
    INNER JOIN Combined_Part ON dbo_PURC_ORDER_LINE.PART_ID = Combined_Part.Part_ID)
    LEFT JOIN [Part Lines] ON (dbo_RECEIVER_LINE.RECEIVER_ID = [Part Lines].Receiver_ID)
        AND (dbo_RECEIVER_LINE.PURC_ORDER_ID = [Part Lines].[PO#])
ORDER BY dbo_Receiver.ID, dbo_RECEIVER_LINE.LINE_NO;

I'm going to go out on a limb here and suggest that you change ALL CASES of Chr(13) in that query to vbCR, because CHR is a function that may have an implied data type of Short Text when dealing with SQL. Since you are concatenating this, do so with something that doesn't call a function. It seems to me that I recall a problem with something that has to call functions because some of them can cause truncation.

One more question: Is this a native Access back-end, an SQL engine back-end, or something else? (Makes a difference because not all SQL engines have the same detailed semantics.)

 

[arnelgp]

change ALL CASES of Chr(13) in that query to vbCR,

vbCr or any VBA constant will not work on Query.

Another suggeston (I don't know if Leban or Browne suggested this), put your "body" (or Memo field) as the First Select Statement, like:

SELECT ."Receiver_ID: " & [Receiver_ID] & Chr(13) & "PO#: " & [dbo_receiver]![Purc_Order_ID] & Chr(13) & "Vendor_ID: " & [Vendor_ID] & Chr(13) & "Vendor Name: " & [dbo_Vendor]![User_5] & Chr(13) & "Country: " & [Country] & Chr(13) & "Part_ID: " & [dbo_Purc_Order_Line]![Part_ID] & Chr(13) & "Description: " & [Combined_Part]![Description] & Chr(13) & "Import Entry: " & [Entry#] & Chr(13) & "Received Date: " & [Received_Date] & Chr(13) & Chr(13) & "Roger Cooper" & Chr(13) & "Spectronics Corporation" AS Body, dbo_Receiver.ID, [etc]


also, see the reasons (1st 2 answers) for truncation here:
Union query truncates the field contents of memo fields - Microsoft Community

 

[The_Doc_Man]

vbCr or any VBA constant will not work on Query.

So make a PARAMETER called pCR and before activating the query, define the parameter as a string. Use THAT in preference to Chr(13). Just avoid calling a function. First, it avoids the headaches known to be associated with calling SQL functions. Second, should execute faster than calling a function.

 

[ebs17]

There is no groupby being used.

With DISTINCT you group across all fields that are listed in the SELECT part. For me, with such a long field list, this is systematic and problematic in terms of performance. The JOINs are used to reproduce content, which then has to be reduced again, the second step then largely without using the index. If you now reduce everything by grouping, you have visibly duplicated too much.

For calculated fields, Jet-SQL assigns its own data types for these fields and uses Long, Double and (Short) Text as standard. The assigned data type Text then only accepts the 256 characters.

One solution might be to use an approach like this:

Len("Import Drawback for " & ... , 60000) AS Body

Jet-SQL has no problem with Chr and other VBA functions, on the contrary, it is a feature that can be used directly by the database engine via the so-called Expression Service VBA standard functions, Access functions (Nz, DLookup) and self-defined functions .

 

[arnelgp]

Len("Import Drawback for " & ... , 60000) AS Body

Len() function will get the Length of the string?

 

[ebs17]

True, it should be Left.

 

[arnelgp]

points to ponder:
Microsoft Access tips: Truncation of Memo fields (allenbrowne.com)

 

[CJ_London]

Having now seen the code I suspect using chr(13) will not be sufficient to make the column a memo field. I suspect you need to use richtext codes rather than chr(13). See this link for examples of seeing how they are used

how to find richtext codes for highlight, underline etc

here is a simple db to make life a bit simpler

www.access-programmers.co.uk

Having done that, either change the body of your email to html or perhaps wrap the resulting string with the plaintext function

might be something like

"<div>Receiver_ID: " & [Receiver_ID] & "<\div><div>PO#: " & [dbo_receiver]![Purc_Order_ID] &"<\div><div>Vendor_ID: " & [Vendor_ID] & ..... & <div>" as HTMLBody

or

plaintext("<div>Receiver_ID: " & [Receiver_ID] & "<\div><div>PO#: " & [dbo_receiver]![Purc_Order_ID] &"<\div><div>Vendor_ID: " & [Vendor_ID] & ..... & <div>") as Body
.

 

[RogerCooper]

I am using an SQL Server back end.

It seems that the simplest solution is to split "Body" into 2 fields. This allows me to use my generic send e-mail function, as opposed to creating some customized code. I have tested splitting and everything worked. Fortunately, I don't have a business need to send arbitrarily long "body" in e-mail messages.

Thanks for everyone who helped me on this.

 

[cheekybuddha]

Some extra questions:

Are [Import Drawback Receiver], Combined_Part or [Part Lines] queries as opposed to tables?

If so, what is their SQL?

If they are tables, are they local Access tables rather than linked SQLServer tables? (Asking since they don't have dbo_ prefix)

 

[RogerCooper]

Import Drawback Receiver is a local Access table. Combined_Part is a query. Part Lines is a linked Access table.

Based upon the references that were mentioned, it seems that a cancatenate operation in query will always cause truncation.