How to create a memo field with a fixed length of 2000 characters

[Dawn Bjork Buzbee]

I need to create an Access field with a fixed length of 2000 characters. This
field is part of a fixed length flat file of billing data that will be
uploaded on a weekly basis to an Oracle application. The field only exists as
a placeholder for possible future fields; the length was set by the developer.

Once I establish a process for generating this field, do I need to fill it
with spaces to "hold" the length?

Thank you in advance for your time and expertise,

 

[Ken Snell [MVP]]

You cannot fix the memo field to a specific length as a property in the
table's design. You'll either need to limit it via programming in the form
that is used for data entry, or limit it via a calculated field (that only
selects the first 2000 characters of the field's contents) in the query that
is used to export the data to a file (I assume text file).

 

[Dawn Bjork Buzbee]

Thank you. I should have been clearer: due to the fact that a memo field can
NOT be defined at a specific length, what would be the best way through
queries, programming, etc. to create an end result of a 2000 character, fixed
length field that is empty or only populated with spaces (whatever is needed
to create a fixed length). No input is needed for this field; forms are only
used to capture some of the client data; other fields are added because they
are required by the system but are not used by the client. Would it work to
use a calculated field such as left([long field],2000) in a query? Would this
work even if the memo field does not contain data? Yes, the output is a text
file.

 

[Ken Snell [MVP]]

This should work for you in a query, even if the memo field is empty:

Left(Nz([long field],""),2000)


--

Ken Snell
<MS ACCESS MVP>

Thank you. I should have been clearer: due to the fact that a memo field
can
NOT be defined at a specific length, what would be the best way through
queries, programming, etc. to create an end result of a 2000 character,
fixed
length field that is empty or only populated with spaces (whatever is
needed
to create a fixed length). No input is needed for this field; forms are
only
used to capture some of the client data; other fields are added because
they
are required by the system but are not used by the client. Would it work
to
use a calculated field such as left([long field],2000) in a query? Would
this
work even if the memo field does not contain data? Yes, the output is a
text
file.
---
Thanks again,
Dawn Bjork Buzbee

You cannot fix the memo field to a specific length as a property in the
table's design. You'll either need to limit it via programming in the
form
that is used for data entry, or limit it via a calculated field (that
only
selects the first 2000 characters of the field's contents) in the query
that
is used to export the data to a file (I assume text file).

 

[Ken Snell [MVP]]

If you need to "pad" the outputted data string, then use this:

Left([long field] & String(2000," "),2000)

--

Ken Snell
<MS ACCESS MVP>

Thank you. I should have been clearer: due to the fact that a memo field
can
NOT be defined at a specific length, what would be the best way through
queries, programming, etc. to create an end result of a 2000 character,
fixed
length field that is empty or only populated with spaces (whatever is
needed
to create a fixed length). No input is needed for this field; forms are
only
used to capture some of the client data; other fields are added because
they
are required by the system but are not used by the client. Would it work
to
use a calculated field such as left([long field],2000) in a query? Would
this
work even if the memo field does not contain data? Yes, the output is a
text
file.
---
Thanks again,
Dawn Bjork Buzbee

You cannot fix the memo field to a specific length as a property in the
table's design. You'll either need to limit it via programming in the
form
that is used for data entry, or limit it via a calculated field (that
only
selects the first 2000 characters of the field's contents) in the query
that
is used to export the data to a file (I assume text file).

 

[peregenem]

You cannot fix the memo field to a specific length as a property in the
table's design.

Actually, you can: use a CHECK constraint a.k.a. validation rule:

CREATE TABLE Test (
data_col MEMO NOT NULL,
CHECK(LEN(data_col) <= 2000)
);

 

[Tim Ferguson]

what would be the best way through
queries, programming, etc. to create an end result of a 2000
character, fixed length field that is empty or only populated with
spaces (whatever is needed to create a fixed length).

If you could, it would fail... Jet places a maximum record size of 2024
bytes, which means 1000 or so characters once you take Unicode into
account. If you were able to create such a field, you would not have any
space to put any useful data.

Besides I don't understand the stuff about

The field only exists as
a placeholder for possible future fields; the length
was set by the developer.

Jet uses variable-length record storage so putting space aside is
meaningless. I think your developer needs to be more explicit about
_what_ he or she wants you to achieve instead of making daft assumptions
about _how_ you are going to do it.

Hope that helps


Tim F

 

[peregenem]

If you could, it would fail... Jet places a maximum record size of 2024
bytes, which means 1000 or so characters once you take Unicode into
account. If you were able to create such a field, you would not have any
space to put any useful data.

Are you sure?

CREATE TABLE Test (
data_col MEMO NOT NULL,
CHECK(LEN(data_col) <= 2000)
)
;
INSERT INTO Test VALUES
('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
;
UPDATE Test SET data_col = data_col & data_col
;
UPDATE Test SET data_col = data_col & data_col
;
UPDATE Test SET data_col = data_col & data_col
;
UPDATE Test SET data_col = data_col & data_col
;
SELECT LEN(data_col) as data_len FROM Test
; -- returns 1600
UPDATE Test SET data_col = data_col & data_col
; -- fails, check bites

CREATE TABLE Test2 (
F1 CHAR(200),
F2 CHAR(200),
F3 CHAR(200),
F4 CHAR(200),
F5 CHAR(200),
F6 CHAR(200),
F7 CHAR(200),
F8 CHAR(200),
F9 CHAR(200),
F10 CHAR(200)
)
;
INSERT INTO Test2 (F1) VALUES
('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
;
UPDATE Test2
SET F2 = F1,
F3 = F1,
F4 = F1,
F5 = F1,
F6 = F1,
F7 = F1,
F8 = F1,
F9 = F1,
F10 = F1
;
SELECT LEN(F1 & F2 & F3 & F4 & F5 & F6 & F7 & F8 & F9 & F10) AS
total_len FROM Test2
; -- returns 2000

 

[Brendan Reynolds]

From the (Access 2003) help file ...
<quote>
Number of characters in a record (excluding Memo and OLE Object fields) when
the UnicodeCompression property of the fields is set to Yes 4,000
</quote>

My understanding is that only a pointer to the Memo and OLE Object fields is
stored within the record, the content of those field types does not
contribute to the record size.

 

[peregenem]

From the (Access 2003) help file ...
<quote>
Number of characters in a record (excluding Memo and OLE Object fields) when
the UnicodeCompression property of the fields is set to Yes 4,000
</quote>

My understanding is that only a pointer to the Memo and OLE Object fields is
stored within the record, the content of those field types does not
contribute to the record size.

Thanks for that. I guessed that any record limit would exclude blob
data.

 

[John Nurick]

If you could, it would fail... Jet places a maximum record size of 2024
bytes, which means 1000 or so characters once you take Unicode into
account.

Up to a point, Lord Copper. According to Help ("Access Specifications")
the maximum "number of characters in a record" excluding Memo and OLE
fields is 2000. Supporting Unicode would seem to require that this
translates to at least 4000 bytes*, and Unicode compression means that
in some circumstances it's possible to get many more than 2000
characters into a record.

I've just tried, with a table consisting of sixteen 255-character text
fields with Unicode compression turned on. With 250 characters in each
of the first 15 fields, the "2000-character" limit kicked in with the
239th character of field 16 - i.e. 3988 characters in the record.

*"at least 4000 bytes": Not all Unicode characters have 16-bit
representations, so it actually takes 8000 bytes to be sure of storing
any 2000-character string. I've no idea how or whether Jet handles this.

 

[Tim Ferguson]

Up to a point, Lord Copper. According to Help ("Access Specifications")
the maximum "number of characters in a record" excluding Memo and OLE
fields is 2000. Supporting Unicode would seem to require that this
translates to at least 4000 bytes*,

Aha: it always used to be 1000 before unicode came in, and I took it that
the extension to 2000 meant you could store the same amount...

Which, of course, prompts the question of whether the OP needs 2000
characters or 2000 bytes. In any case, it's moot.


I seem to be running across an awful lot of Evelyn Waugh recently, for one
reason or another. Must be approaching my own decline and fall <g>


All the best


Tim F

 

[Dawn Bjork Buzbee]

Thank you all for the detailed and helpful responses. I will put your advice
to work to create the solution.

 

[peregenem]

Thank you all for the detailed and helpful responses. I will put your advice
to work to create the solution.

How about some 'helper' procedures? The following is Jet 4.0 (yes, it
really is not T-SQL <g>) meaning it has to be executed via the OLE DB
provider for Jet 4.0, so either use the provider directly (e.g. in VBA
code use CurrentProject.Execute "sql goes here") or put Access into
'ANSI-92' mode
(http://office.microsoft.com/en-us/assistance/HP030704831033.aspx).
Watch for the two consecutive single quotes ('') representing an empty
string:

CREATE TABLE Test (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col NTEXT NOT NULL,
CHECK (LEN(data_col) = 2000)
)
;
CREATE PROCEDURE TestInsert
(
:key_col INTEGER,
:data_col NTEXT = ''
)
AS
INSERT INTO Test (key_col, data_col)
VALUES key_col,
IIFdata_col IS NULL, '', :data_col)
& SPACE$(2000 - LEN(IIFdata_col IS NULL, '', :data_col)))
)
;
CREATE PROCEDURE TestUpdate
(
:key_col INTEGER,
:data_col NTEXT = ''
)
AS
UPDATE Test
SET data_col =
IIFdata_col IS NULL, '', :data_col)
& SPACE$(2000 - LEN(IIFdata_col IS NULL, '', :data_col)))
WHERE key_col = IIFkey_col IS NULL, key_col, :key_col)
;

EXECUTE TestInsert 1, 'Hello'
;
EXECUTE TestInsert 2, ''
;
EXECUTE TestInsert 3
;
SELECT key_col, data_col, LEN(data_col) as data_len
FROM Test
;
EXECUTE TestUpdate NULL, 'Bye'
;
SELECT key_col, data_col, LEN(data_col) as data_len
FROM Test
;

The next thing to do is to REVOKE permissions on the base table and
GRANT permissions only on the procedures so that the only way of adding
data is via the procedures, meaning the data is under the control of
the database programmer (and not the front end application developer
<g>).