error after changing field type

[Richard]

Hi

I changed some fields from text to memo as the entries was getting past the
255 limit. The problem is that now when I open the reports based of the
table, a dialog comes up quoting error and closes the database.

It works perfectly before I changed the field type. Help greatly
appreciated.

Many thanks in advance
Richard

 

[Van T. Dinh]

Pst the Error number / Error text you get ...

 

[Richard]

Hi Van

Its the Access encountered a problem dialog box, where it prompts us to send
the details to Microsoft, and then it closes the db, do a back-up and
compact the db.

Richard

 

[Van T. Dinh]

That message/dialog doesn't help ...

Post the SQL String of the RecordSource because my guess is that if anything
wrong with changing from Text to Memo, it will be the RecordSource.

 

[Richard]

HI Van

The record is based on this query:

SELECT EvaluationDetails.EvaluationId, "A" AS Expr1, "The topic I like most
in this workshop:" AS Expr2, EvaluationDetails.FavTopic AS Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.FavTopic) Is Not Null));
UNION SELECT EvaluationDetails.EvaluationId, "B" AS Expr1, "I will describe
this workshop to my friends as:" AS Expr2, EvaluationDetails.DesWs AS
Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.DesWs) Is Not Null));

UNION SELECT EvaluationDetails.EvaluationId, "C" AS Expr1, "I am interested
in other workshops:" AS Expr2, EvaluationDetails.IntWs AS Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.IntWs) Is Not Null));

UNION SELECT EvaluationDetails.EvaluationId, "D" AS Expr1, "The topic I
found less useful:" AS Expr2, EvaluationDetails.NoTopic AS Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.NoTopic) Is Not Null));

UNION SELECT EvaluationDetails.EvaluationId, "E" AS Expr1, "Other
Comments:" AS Expr2, EvaluationDetails.Comments AS Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.Comments) Is Not Null));

UNION SELECT EvaluationDetails.EvaluationId, "F" AS Expr1, "Comments for
guest speaker: " & forms!evaluation!txtguestspeaker AS Expr2,
EvaluationDetails.CommentsGuest1 AS Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.CommentsGuest1) Is Not Null));

UNION SELECT EvaluationDetails.EvaluationId, "F" AS Expr1, "Comments for
guest speaker: " & forms!evaluation!txtguestspeaker2 AS Expr2,
EvaluationDetails.CommentsGuest2 AS Comments
FROM EvaluationDetails
WHERE
(((EvaluationDetails.EvaluationId)=[Forms]![Evaluation]![txtEvaluationId])
AND ((EvaluationDetails.CommentsGuest2) Is Not Null));

The query works fine. On debugging, the error occurs on this line when I
look for duplicates in the same field:

ingRec = DCount("comments", "QryEvaluationDupCount", "Comments=" &
strcomments & " And Expr1=" & """" & Me.txtExpr1 & """")

if there are duplicates, the comments will be printed as "duplicate"
followed by a bracketed "ingRec"

Hope this will be clear enough for you.

Thanks again
Richard

 

[Richard]

Hi Van

I managed to overcome the problem.

I changed the Dcount("comments" to "*" and all is back to normal.

Many thanks for your time.

Richard

 

[Van T. Dinh]

Yes. I asked for the SQL to check for Domain Aggregate functions using the
Memo Field.