Saved query doesn't select all info in Memo field.

[Andrew]

I have several saved queries many which pull information from Memo field.
However, only the first 255 character are being retreived, but need them all.
Is there away to specify the field data type for a query field in the
QueryDef?

 

[Dalt]

What version of Access are you using? I'm curious if there is a fix as well
because i'd hate to be limited like this.

 

[Andrew]

I'm using MS Access 2002 with a database in 2000 format.

Basically, here's what happens:

The user makes crteria selections for the "WHERE" part of the SQL query.
This is then submitted to the any one of 15 (or so) reports. Each report is
based on it's own Saved Query. The saved query is rewritten with all the
fields required for the report and all the necessary relationships for the
criteria selected by the following:

Public Function ChangeReportRecordSource(strReportName As String,
strQueryName As String, sqlString As String, strWhere As String)
'Change Report Record Source Function.
'Function to change the Query Definition of the query assigned to the
RecordSource Property in a selected report.

'Dimension variables.
Dim theQuery As QueryDef

Set theQuery = CurrentDb.QueryDefs(strQueryName)
theQuery.SQL = sqlString
RefreshDatabaseWindow
Set theQuery = Nothing

DoCmd.OpenReport strReportName, acViewPreview, , , acWindowNormal, strWhere

End Function

Then at the end the report is opened.

I need to retreive the whole memo field when one is required on a report.

 

[John Spencer]

Since we don't know what the query string looks like it is impossible to
tell if the query is at fault or if something else is happening.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Andrew]

Thanks for help. I was going to give you an example, but as I was looking at
it I relized the DISTINCT key work in the query was screwing things up. When
I took it out and tried again, the problem went away. Now I just have to
prevent duplicate reocrds from being printed on the reports.

Andrew.

 

[David W. Fenton]

Memo fields have some restrictions because the data is not
actually stored in the actual table (only a pointer to it is).
This is why you cannot index them for example.

This also imposes restrictions to what you can do with them in
queries. One example is that in a Totals query you cannot group
on them. In Access 97 and older you would be given an error if
you tried to do these things.

Well, not directly, but it was easy enough to replace the memo
fields with Left(MyMemoField, N), where N is some large number that
will return the full length of the content in all or most of your
memo fields.

In Access 2000 MS decided it would be better to allow you to do
these things but implementing them by truncating all but the first
255 characters. In my opinion this was a stupid decision, but
there you go.

Yes, I think it would have been better to have given you the same
error message and implemented some property that the error message
would allow you to choose that would truncate the fields and sort on
them. Something like:

You cannot sort/group on memo fields. Would you like to have
Access truncate the fields at 255 characters so that it can
sort/group on your memo field? YES/NO

That would mean that you couldn't do it without intending to, and at
least theoretically you'd know why the fields were being truncated.
On the other hand, I don't know how you'd implement something like
that, since you'd need to expose the result somewhere so you could
undo it without deleting and recreating the column in your query.

 

[David W. Fenton]

I was going to give you an example, but as I was looking at
it I relized the DISTINCT key work in the query was screwing
things up. When I took it out and tried again, the problem went
away. Now I just have to prevent duplicate reocrds from being
printed on the reports.

The easiest way to fix that and still get unique records is to
replace your memo field with this:

Left(MyMemoField, N)

where N is some number large enough to return all the data in your
longest memo fields. Then your distinct won't cause any truncation
and you'll still get unique records.

This was what we had to do back in the days before Access got so
smart it started truncating things without asking us.