Can't Include Memo or OLE Object

B

bdehning

I converted an Access 2000 database that worked perfectly back to 97 and now
in 97 I have a query that syas this:

Can't include Memo or OLE Object when you select unique values
(Remarks-Remarks).

Can someone help me understand what this is saying and what can I do to make
this query work in Access 97 that does work in 2000.

Brian
 
D

Dale Fye

Basically, what it is saying is that you included a memofield, or an OLE
field in a query that uses a DISTINCT clause; which Access does not support.
For one thing, it does not make a lot of sense to include memo or OLE object
fields in this type of query, since you will almost never have idenitcal
data in these types of fields. Solutions:

1. Take the memo or OLD field out of the query.
2. If you really need the memo field in the query, try using the LEFT
function, to parse some number of characters from the memo field, that might
work. Something like:

SELECT DISTINCT FieldA, FieldB, LEFT(memoField, LEN(memoField)) as
MemoToChar
FROM your table
 
B

bdehning

SELECT DISTINCT Remarks.[Remarks #], Remarks.Remarks, Remarks.[Date Remarks
Entered], Remarks.[Remark Entered By], [Account Information].[Account Name],
[Account Information].[Policy Number], Remarks.Evaluation,
Remarks.Recommendations, Location.[Location Servicing Division],
IIf(Len(Trim$([Remarks] & ""))=0,"No Comments",[Remarks]) AS NonBlankComments
FROM ([Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]) INNER JOIN Remarks
ON [Account Information].[Policy Number] = Remarks.[Policy Number];

Can You help. I tried to place what you gave me but gave data error. The
memo field is remarks.remarks
 
J

John Vinson

SELECT DISTINCT Remarks.[Remarks #], Remarks.Remarks, Remarks.[Date Remarks
Entered], Remarks.[Remark Entered By], [Account Information].[Account Name],
[Account Information].[Policy Number], Remarks.Evaluation,
Remarks.Recommendations, Location.[Location Servicing Division],
IIf(Len(Trim$([Remarks] & ""))=0,"No Comments",[Remarks]) AS NonBlankComments
FROM ([Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]) INNER JOIN Remarks
ON [Account Information].[Policy Number] = Remarks.[Policy Number];

Can You help. I tried to place what you gave me but gave data error. The
memo field is remarks.remarks

Just remove the DISTINCT; or if there may be multiple values of
Location and you want to see only one Remark, use a Totals query
grouping by all the other fields and using First() for Remark.

John W. Vinson[MVP]
 
B

bdehning

John, that did solve the duplicate row issue but what if I wanted to allow
the memo field "Remarks" to have more than 255 characters in a query or
ultimately on a report?

How would one use 2 queries to bring the data together to be able to show on
a report if memo fields are critical?

John Vinson said:
SELECT DISTINCT Remarks.[Remarks #], Remarks.Remarks, Remarks.[Date Remarks
Entered], Remarks.[Remark Entered By], [Account Information].[Account Name],
[Account Information].[Policy Number], Remarks.Evaluation,
Remarks.Recommendations, Location.[Location Servicing Division],
IIf(Len(Trim$([Remarks] & ""))=0,"No Comments",[Remarks]) AS NonBlankComments
FROM ([Account Information] LEFT JOIN Location ON [Account
Information].[Policy Number] = Location.[Policy Number]) INNER JOIN Remarks
ON [Account Information].[Policy Number] = Remarks.[Policy Number];

Can You help. I tried to place what you gave me but gave data error. The
memo field is remarks.remarks

Just remove the DISTINCT; or if there may be multiple values of
Location and you want to see only one Remark, use a Totals query
grouping by all the other fields and using First() for Remark.

John W. Vinson[MVP]
 
J

John Vinson

John, that did solve the duplicate row issue but what if I wanted to allow
the memo field "Remarks" to have more than 255 characters in a query or
ultimately on a report?

Removing DISTINCT, or using FIRST for the memo field, should give you
the un-truncated memo.

John W. Vinson[MVP]
 
B

bdehning

John, I should have explained this better as I ned to figure out to create
queries to use memos fields better. The first and remove distinct solved
this queries issue.

However, I use this query as part of a union query which I know is a
problem. If one has 2 memo fields that need to be merged or union together
how would one go about this.

I currently have table relationships below

Account Information 1-many Location
Location 1-many Service calls
Account Information 1-many Remarks

Remarks table has memo field Remarks and Service Calls Table has memo field
Comments. There is actually another memo field that is used in Service Calls
as well. Remarks and Comments are the two fields which need to be brought
together.

Is there a better way to structure the database then what I have to be able
to use memo fields more effectively. My use of unions and sorting are
causing issues as you would expect. I need the ability to have more than 255
characters which adds to the problem.

Hope this makes some sense or am I still an Access dummy.
 
J

John Vinson

John, I should have explained this better as I ned to figure out to create
queries to use memos fields better. The first and remove distinct solved
this queries issue.

However, I use this query as part of a union query which I know is a
problem. If one has 2 memo fields that need to be merged or union together
how would one go about this.

You can use UNION ALL instead of just UNION; that preserves memo
fields (but does not remove duplicates).
I currently have table relationships below

Account Information 1-many Location
Location 1-many Service calls
Account Information 1-many Remarks

I'm wondering what you're UNIONing. This looks properly normalized, as
best I can tell!


John W. Vinson[MVP]
 
B

bdehning

John.

Here is my union and I do use Union all.

When it runs I get funny symbols in comments field when memo fields being
brought together had more than 255 Characters. Any suggestions on that.

SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT INFORMATION].[ACCOUNT
NAME],[COMMENTS],[DATE WRITTEN REPORT
SENT],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [ACCOUNT SERVICE RECORD FOR REMARKS-COMMENTS]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*")

UNION ALL SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT
INFORMATION].[ACCOUNT NAME],[FirstofREMARKS],[DATE REMARKS
ENTERED],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [REMARKS Test]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*");
 
J

John Vinson

John.

Here is my union and I do use Union all.

When it runs I get funny symbols in comments field when memo fields being
brought together had more than 255 Characters. Any suggestions on that.

SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT INFORMATION].[ACCOUNT
NAME],[COMMENTS],[DATE WRITTEN REPORT
SENT],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [ACCOUNT SERVICE RECORD FOR REMARKS-COMMENTS]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*")

UNION ALL SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT
INFORMATION].[ACCOUNT NAME],[FirstofREMARKS],[DATE REMARKS
ENTERED],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [REMARKS Test]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*");

Are [ACCOUNT SERVICE RECORD FOR REMARKS-COMMENTS] and [REMARKS Test]
tables, or queries? Do they have the problem with the memo fields when
run individually?

John W. Vinson[MVP]
 
B

bdehning

Both are queries.. They do not have similiar problems when run individually

John Vinson said:
John.

Here is my union and I do use Union all.

When it runs I get funny symbols in comments field when memo fields being
brought together had more than 255 Characters. Any suggestions on that.

SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT INFORMATION].[ACCOUNT
NAME],[COMMENTS],[DATE WRITTEN REPORT
SENT],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [ACCOUNT SERVICE RECORD FOR REMARKS-COMMENTS]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*")

UNION ALL SELECT [ACCOUNT INFORMATION].[POLICY NUMBER],[ACCOUNT
INFORMATION].[ACCOUNT NAME],[FirstofREMARKS],[DATE REMARKS
ENTERED],[EVALUATION],[RECOMMENDATIONS],[LOCATION SERVICING
DIVISION],[NONBLANKCOMMENTS]
FROM [REMARKS Test]
WHERE ([ACCOUNT INFORMATION].[Account Name] like [Enter Account Name] & "*");

Are [ACCOUNT SERVICE RECORD FOR REMARKS-COMMENTS] and [REMARKS Test]
tables, or queries? Do they have the problem with the memo fields when
run individually?

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top