Query with memo field shows only two chinese charcters when linked

[Bill]

A query of a table has two memo fields.
The single query shows the text of the memo fields ok.
That is you can read the full text.
When this query is linked to another, the two memo fields suddenly develope
only two chineses charcters or just show one square in them.
The text is gone and replaced by a square or the two chinese charcaters.

I have also run a make table of the original table.
Checked that the full text is there.
In the query the text shows.
If I link the table in a new query - text gone.

What gives?
Is there a work around?

Thanx
Bill

 

[Ken Snell MVP]

Show the SQL statement of the query that's "misbehaving". Identify which
field(s) is/are the memo field(s) in the query.

 

[Bill]

Greetings
The two memo fields in question are:

AmendmentDetails
ReasonforAmendment

The SQL is as follows:

SELECT PurchasingJN_NAF.PurchaseOrder, PurchasingJN_NAF.Currency,
PurchasingJN_NAF.[SumOfSumOfSum Of ExtPrice] AS PO_Sum,
PurchasingJN_NAF.ExRate, PurchasingJN_NAF.NZD,
NuConAmendment_Make.Formnumber, NuConAmendment_Make.OrderRef,
NuConAmendment_Make.OldOrderTotal, NuConAmendment_Make.NewOrderTotal,
NuConAmendment_Make.Rev, NuConAmendment_Make.AmendmentDetails,
NuConAmendment_Make.ReasonforAmendment
FROM PurchasingJN_NAF INNER JOIN NuConAmendment_Make ON
PurchasingJN_NAF.PurchaseOrder = NuConAmendment_Make.OrderRef
GROUP BY PurchasingJN_NAF.PurchaseOrder, PurchasingJN_NAF.Currency,
PurchasingJN_NAF.[SumOfSumOfSum Of ExtPrice], PurchasingJN_NAF.ExRate,
PurchasingJN_NAF.NZD, NuConAmendment_Make.Formnumber,
NuConAmendment_Make.OrderRef, NuConAmendment_Make.OldOrderTotal,
NuConAmendment_Make.NewOrderTotal, NuConAmendment_Make.Rev,
NuConAmendment_Make.AmendmentDetails, NuConAmendment_Make.ReasonforAmendment
HAVING (((NuConAmendment_Make.Formnumber) Is Not Null) AND
((NuConAmendment_Make.OrderRef) Is Not Null));

Using access 2003 front end.
Tables in the backend involved, are access 2000

Best regards
Bill

 

[Ken Snell MVP]

This is a common situation when you include a memo field in the GROUP BY
clause of a query. Try this query, where we use the First aggregate function
for the memo fields:

SELECT PurchasingJN_NAF.PurchaseOrder, PurchasingJN_NAF.Currency,
PurchasingJN_NAF.[SumOfSumOfSum Of ExtPrice] AS PO_Sum,
PurchasingJN_NAF.ExRate, PurchasingJN_NAF.NZD,
NuConAmendment_Make.Formnumber, NuConAmendment_Make.OrderRef,
NuConAmendment_Make.OldOrderTotal, NuConAmendment_Make.NewOrderTotal,
NuConAmendment_Make.Rev,
First(NuConAmendment_Make.AmendmentDetails) AS AmendmentDetails,
First(NuConAmendment_Make.ReasonforAmendment) AS ReasonForAmendment
FROM PurchasingJN_NAF INNER JOIN NuConAmendment_Make ON
PurchasingJN_NAF.PurchaseOrder = NuConAmendment_Make.OrderRef
GROUP BY PurchasingJN_NAF.PurchaseOrder, PurchasingJN_NAF.Currency,
PurchasingJN_NAF.[SumOfSumOfSum Of ExtPrice], PurchasingJN_NAF.ExRate,
PurchasingJN_NAF.NZD, NuConAmendment_Make.Formnumber,
NuConAmendment_Make.OrderRef, NuConAmendment_Make.OldOrderTotal,
NuConAmendment_Make.NewOrderTotal, NuConAmendment_Make.Rev
HAVING (((NuConAmendment_Make.Formnumber) Is Not Null) AND
((NuConAmendment_Make.OrderRef) Is Not Null));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Greetings
The two memo fields in question are:

AmendmentDetails
ReasonforAmendment

The SQL is as follows:

SELECT PurchasingJN_NAF.PurchaseOrder, PurchasingJN_NAF.Currency,
PurchasingJN_NAF.[SumOfSumOfSum Of ExtPrice] AS PO_Sum,
PurchasingJN_NAF.ExRate, PurchasingJN_NAF.NZD,
NuConAmendment_Make.Formnumber, NuConAmendment_Make.OrderRef,
NuConAmendment_Make.OldOrderTotal, NuConAmendment_Make.NewOrderTotal,
NuConAmendment_Make.Rev, NuConAmendment_Make.AmendmentDetails,
NuConAmendment_Make.ReasonforAmendment
FROM PurchasingJN_NAF INNER JOIN NuConAmendment_Make ON
PurchasingJN_NAF.PurchaseOrder = NuConAmendment_Make.OrderRef
GROUP BY PurchasingJN_NAF.PurchaseOrder, PurchasingJN_NAF.Currency,
PurchasingJN_NAF.[SumOfSumOfSum Of ExtPrice], PurchasingJN_NAF.ExRate,
PurchasingJN_NAF.NZD, NuConAmendment_Make.Formnumber,
NuConAmendment_Make.OrderRef, NuConAmendment_Make.OldOrderTotal,
NuConAmendment_Make.NewOrderTotal, NuConAmendment_Make.Rev,
NuConAmendment_Make.AmendmentDetails,
NuConAmendment_Make.ReasonforAmendment
HAVING (((NuConAmendment_Make.Formnumber) Is Not Null) AND
((NuConAmendment_Make.OrderRef) Is Not Null));

Using access 2003 front end.
Tables in the backend involved, are access 2000

Best regards
Bill

Show the SQL statement of the query that's "misbehaving". Identify which
field(s) is/are the memo field(s) in the query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/