Query field property problem

[Rob]

I have a query that I designed to return a form consisting
of records that match the criteria entered. More than one
field in the table are 'memo' type. The manual states
plainly that the query field will have the same properties
as the table field, yet this isn't the case. I can get
only 255 characters of data through the query and into a
report or form. That's not enough. There seems no built-
in way to change the field properties of the query. Any
help would be much appreciated.

R

 

[John Spencer (MVP)]

I'm going to have to guess that inclusion of [Ditches, oneline/ditch/section]
table is causing you to get the multiple lines back.

Your best bet might be to drop the memo fields from the existing query (or a
copy) and save it. Then use the query you've saved in another query linking to
the [Ditch Data Table] to pick up the memo fields. I'm guessing the the Ditch
Number is unique.

QryOne:
SELECT DISTINCT [Q-Secs].Complete,
[Ditch Data Table].[Ditch Number]
FROM ([Q-Secs] INNER JOIN [Ditches, one line/ditch/section]
ON [Q-Secs].Complete = [Ditches, one line/ditch/section].Section)
INNER JOIN [Ditch Data Table]
ON [Ditches, one line/ditch/section].[Ditch Number] =
[Ditch Data Table].[Ditch Number]
WHERE ((([Q-Secs].Range)=[Enter Range Number]) AND
(([Q-Secs].Twp)=[Enter Township Number]) AND
(([Q-Secs].Sec)=[Enter Section Number]))
ORDER BY [Q-Secs].Complete;

Then in the second query.

SELECT [QryOne].Complete,
[OryOne].[Ditch Number],
[Ditch Data Table].[Ditch Name],
[Ditch Data Table].[Ditch Location],
[Ditch Data Table].Discharge,
[Ditch Data Table].[Documentation References],
[Ditch Data Table].[Financial Data],
[Ditch Data Table].[Ditch Record References],
[Ditch Data Table].[Flat Files Technical Docs],
[Ditch Data Table].[Ditch Files Technical Docs],
[Ditch Data Table].[Ditch status history],
[Ditch Data Table].[Repair History],
[Ditch Data Table].[Historical notes]
FROM QryOne Inner Join [Ditch Data Table] ON
QryOne.[Ditch Number] = [Ditch Data Table].[Ditch Number]
ORDER BY [QryOne].Complete;

The sql appears below. You were right, I used 'select
distinct'. That was because the original query was
returning the appropriate records four times. Having
examined the records and the sql for that, I was unable to
come up with any decent reason for the quadruplications.
Feel free to examine the language for the source of that
odd little output artifact - especially if you're
preparing to tell me I have to move away from 'SELECT
DISTINCT'.

The statement here is long, but I involved three tables
and many fields of the records I wanted returned. Most of
the language is simply piling those fields into the output.

Thanks very much for your interest.

Rob

SELECT DISTINCT [Q-Secs].Complete, [Ditch Data Table].
[Ditch Number], [Ditch Data Table].[Ditch Name], [Ditch
Data Table].[Ditch Location], [Ditch Data
Table].Discharge, [Ditch Data Table].[Documentation
References], [Ditch Data Table].[Financial Data], [Ditch
Data Table].[Ditch Record References], [Ditch Data Table].
[Flat Files Technical Docs], [Ditch Data Table].[Ditch
Files Technical Docs], [Ditch Data Table].[Ditch status
history], [Ditch Data Table].[Repair History], [Ditch Data
Table].[Historical notes]
FROM ([Q-Secs] INNER JOIN [Ditches, one
line/ditch/section] ON [Q-Secs].Complete = [Ditches, one
line/ditch/section].Section) INNER JOIN [Ditch Data Table]
ON [Ditches, one line/ditch/section].[Ditch Number] =
[Ditch Data Table].[Ditch Number]
WHERE ((([Q-Secs].Range)=[Enter Range Number]) AND (([Q-
Secs].Twp)=[Enter Township Number]) AND (([Q-Secs].Sec)=
[Enter Section Number]))
ORDER BY [Q-Secs].Complete;

-----Original Message-----
Can you post the SQL statement? If you have used a totals query or the DISTINCT
clause in your query or a UNION query, then memo fields will be truncated to 255
characters.

Also, if you have applied any formatting to the Memo Field, it will be
truncated.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

.