Increase field size

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:)
 
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.
 
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)
 
Rick said:
There are some restrictions on the memo
field though. You cannot index it...
Click to expand...

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.
...join on it in a query...
Click to expand...

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);
...or use group by on it
in a query.
Click to expand...

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.

--
 
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.
 
Rick said:
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.
Click to expand...

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.

Jamie.

--
 
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?
Click to expand...

I'll answer my own question: because the text values are less than 255
characters!

No biggie, I just made a misstatement based on the inability to count
beyond 200 <g>.

Jamie.

--
 
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.
 
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)
 
david@epsomdotcomdotau said:
That is to say, you can try (in Access 2000+), to index, join, or group,
but... Access will only use the first 255 characters
Click to expand...

The difference between 'relational database' and 'SQL DBMS' is the
latter has features based on pragmatics.

One such decision in the Jet implementation is to limit certain
comparisons to 255 characters (max 510 bytes). Other products apply
limits e.g. SQL Server 2005 indexes are still limited to 900 bytes.

Another similar decision: if NULL = NULL does not return true, why do
NULLs group together in a GROUP BY? The answer is, because it makes the
most practical sense to work that way.

Also bear in mind we are talking about 'out of the box' limits. If we
need to go beyond these limits we can get creative. Here's an example
that works around the limit in a JOIN and a UNIQUE constraint to double
the limit from 255 to 510 (and could be extended further):

CREATE TABLE Test (
memo_col MEMO NOT NULL,
CONSTRAINT PK__Test
CHECK (1 =
(
SELECT COUNT(*)
FROM Test AS T2
WHERE
MID(Test.memo_col, 1, 255) = MID(T2.memo_col, 1, 255)
AND MID(Test.memo_col, 256, 510) = MID(T2.memo_col, 256, 510)
))
);
Which is enough to be dangerous.
Click to expand...

I hope you are not suggesting that MEMO data (or UNION constructs or
NULLs etc) be 'verboten' just because of some limitations based on
pragmatics.
Some queries
will return rubbish random characters when you do.
Click to expand...

Now this I must see! Please post how I can reproduce this. Thanks.

Jamie.

--
 
david@epsomdotcomdotau said:
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.
Click to expand...

Rather than designating whole areas 'off limits', I prefer to be aware
of the limitations and see how far I can get.
From a practical point of view, I'd restrict my use of MEMO to things
Click to expand...
like free text 'notes' for users i.e. I wouldn't want to UNIQUE, JOIN,
UNION, GROUP, SORT, etc but I would want to limit the length, restrict
the contents (allow Unicode and you will get Unicode <g>), etc.

You comments reminds me of something on Allen Browne's site
(http://allenbrowne.com/tips.html):

"These can ALL fail: PARAMETERS clause; SELECT clause; DISTINCT
predicate; FROM clause; WHERE clause; GROUP BY clause; ORDER BY clause.
Getting hard to write a query without considering the bugs?"

I realise his tongue is firmly embedded in his cheek but the point is
there are limitations and bugs throughout the product and you just have
to get on with things.
I could count on two fingers the number of new features in
Jet 4.0 that were actually useful
Click to expand...

Ah yes, table-level CHECK constraints, the DECIMAL data type (including
native decimals), CASCADE in DRI, improved SQL DDL syntax (e.g. CREATE
PROCEDURE with default values for parameters), Unicode compression,
@@IDENTITY, ... sorry, did you say two fingers <g>?

Jamie.

--
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to send an email via an Address in an Access2003 field? 1
Text Field Size - Access 2007 2
Age of Empires: Definitive Edition 4
Field Sizes 5
Tablet size: I love the 8 inches 1
Increase field size from code 2
The Last of Us - Part I (PC) 6
Counting Remaining Text Characters 2

Back
Top