Query won't run

#1

This query runs OK

SELECT Hosts.*
FROM Hosts;

but

SELECT DISTINCT Hosts.*
FROM Hosts;
gives an error (No number) "The field is too small to accept the amount of
data you attempted to add. Try inserting or pasting less data."

Any ideas please

Phil

#2

Phil wrote:

Quote:

This query runs OK

SELECT Hosts.*
FROM Hosts;

but

SELECT DISTINCT Hosts.*
FROM Hosts;
gives an error (No number) "The field is too small to accept the
amount of data you attempted to add. Try inserting or pasting less
data."

Any ideas please

Phil
You likely have a Memo field in your table. DISTINct does not work with Memo

fields. You have two options:
1. Explicitly list the fields in your table, ommitting the Memo field
2. If you must include the Memo field, use the Left function to only include
the first 255 characters:
select distinct field1, field2, left(memofield,255) as memo_extract ...

#3

On 28/11/2010 18:19:17, "Bob Barrows" wrote:

Quote:

Phil wrote:
This query runs OK

SELECT Hosts.*
FROM Hosts;

but

SELECT DISTINCT Hosts.*
FROM Hosts;
gives an error (No number) "The field is too small to accept the
amount of data you attempted to add. Try inserting or pasting less
data."

Any ideas please

Phil
You likely have a Memo field in your table. DISTINct does not work with
Memo fields. You have two options:
1. Explicitly list the fields in your table, ommitting the Memo field
2. If you must include the Memo field, use the Left function to only
include the first 255 characters:
select distinct field1, field2, left(memofield,255) as memo_extract ...

Thanks Bob. Spot on.

Didn't know that

Phil

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Query won't run

comp.databases.ms-access comp.databases.ms-access

Query won't run - 11-28-2010 , 11:06 AM

Re: Query won't run - 11-28-2010 , 12:19 PM

Re: Query won't run - 11-28-2010 , 04:03 PM