Increase field size

L

Lori

I am a computer idiot and I need to know how to increase a firld size to
accept more then 255 characters. Is that possible? If so please tell me
exactly what I need to do to do so. Thank you so much:)
 
R

Rick Brandt

Lori said:
I am a computer idiot and I need to know how to increase a firld size
to accept more then 255 characters. Is that possible? If so please
tell me exactly what I need to do to do so. Thank you so much:)
Click to expand...

Change the type from text to memo. There are some restrictions on the memo
field though. You cannot index it, join on it in a query, or use group by on it
in a query.
 
G

Guest

restrictions on the memo field though. You cannot index it, join
on it in a query, or use group by on it in a query.
Click to expand...

That is to say, you can try (in Access 2000+), to index, join, or group,
but (1) Access will only use the first 255 characters, and (2) Some queries
will return rubbish random characters when you do.

That is to say, it will appear to work with some data in some queries.

Which is enough to be dangerous.

(david)
 
R

Rick Brandt

onedaywhen said:
I don't think this is correct.

CREATE TABLE Test1 (
memo_col MEMO NOT NULL
)
;
INSERT INTO Test1 (memo_col) VALUES ('One')
;
CREATE TABLE Test1 (
memo_col MEMO NOT NULL
)
;
INSERT INTO Test2 (memo_col) VALUES ('One')
;

-- create UNIQUE index
TABLE Test1 ADD
CONSTRAINT uq__test UNIQUE (memo_col);

-- create regular index
CREATE INDEX idx__test
ON Test2 (memo_col);

Both index appear in the schema (e.g. ADO's OpenSchema method).

Are you alluding to something in implementation e.g. are the indexes
not utilized in some way? Details please.


This is technically correct but from a practical point of view the
data can be cast to another type e.g. (noting the OP is interested in
text data):

SELECT T1.memo_col, T2.memo_col
FROM Test1 AS T1
LEFT JOIN Test2 AS T2
ON CSTR(T1.memo_col) = CSTR(T2.memo_col);


I think this is a misstatement. Example: (noting the OP is s

SELECT T1.memo_col, COUNT(*) AS tally
FROM Test1 AS T1
GROUP BY T1.memo_col;

works as expected and without error.

Jamie.
Click to expand...

All of those things were NOT allowed at all in Access 07 and earlier. In Access
2000 and later they APPEAR to be allowed but in reality only the first 255
characters are used in all of those situations. In my opinion that means the
restrictions still apply.
 
R

Rick Brandt

onedaywhen said:
If you think "You cannot index it" still applies and "only the first
255 characters are used" then why do the following produce no errors
in creation and usage?

CREATE TABLE Test1 (
memo_col MEMO NOT NULL
UNIQUE)
;
CREATE INDEX idx__test ON Test1 (memo_col)
;
INSERT INTO Test1 (memo_col) VALUES
('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789A')

;
INSERT INTO Test1 (memo_col) VALUES
('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789B')

;

No biggie, I just think you made a misstatement based on Access 97
behaviour.
Click to expand...

From Access 2003 help...
**********************
You can sort or group on a Text field or a Memo field, but Access only uses
the first 255 characters when you sort or group on a Memo field.
**********************

In a test table I created a unique index on a memo field and then entered
two records with 255 repitions of the character "X". The second record was
not allowed even if I added additional characters after the 255th so clearly
the index in only using those positions.

I will concede that "cannot" was perhaps over-stated, but their are
"limitations" compared to regular text fields.
 
G

Guest

But, at the risk of repeating my self :~), this feature is buggy,
and will return garbage characters from some queries. Do
not sort, group, join or union on a memo field if more than
one table is involved. Exercise discipline and good judgement
by not sorting or grouping even if only one table is involved.

I could count on two fingers the number of new features in
Jet 4.0 that were actually useful, and this is not one of them.

(david)
 
Top