whitespace string as primary key

shallow

Hi all,

msaccess (2003) seems to be unable to cope with whitespace strings:

let's assume a table with a text field as primary key.

now enter a new record that has a number of spaces and only spaces in
the primary key field.

msaccess will complain and claim "index or primary key cannot contain
a Null value".
a string of spaces is clearly not a null value.
am i missing something ?
is there some option to control this behaviour?

any help greatly appreciated

Nov 13 '05 #1

Rick Brandt

"shallow" <de*********@yahoo.com> wrote in message
news:e3*************************@posting.google.co m...

Hi all,

msaccess (2003) seems to be unable to cope with whitespace strings:

let's assume a table with a text field as primary key.

now enter a new record that has a number of spaces and only spaces in
the primary key field.

msaccess will complain and claim "index or primary key cannot contain
a Null value".
a string of spaces is clearly not a null value.
am i missing something ?
is there some option to control this behaviour?

What you're missing is that through the standard user interface Access
automatically trims all trailing spaces. In the case of an entry with
nothing but spaces all of it is trimmed.

Why on earth would you want a PK value of blanks? I suppose if you created
the record with an append query the value might take.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #2

Keith Wilby

de*********@yahoo.com (shallow) wrote:

msaccess (2003) seems to be unable to cope with whitespace strings:

I've just tried it in A97 with the same result so it isn't version
specific. I'm curious - why do you want to be able to do this?

Regards,
Keith.

Nov 13 '05 #3

david epsom dot com dot au

The Access IDE will trim strings.

If you have a field set to not accept empty strings, then empty strings will
automatically be converted to nulls.
If the field is a primary key field, the null will be rejected.

Jet/ADO/DAO do not trim strings, so you can add white space strings to a Jet
database by using ADO/DAO.

Some types of SQL Server string fields trim strings, and will not allow you
enter whitespace strings.

(david)

"shallow" <de*********@yahoo.com> wrote in message
news:e3*************************@posting.google.co m...

Hi all,

msaccess (2003) seems to be unable to cope with whitespace strings:

let's assume a table with a text field as primary key.

now enter a new record that has a number of spaces and only spaces in
the primary key field.

msaccess will complain and claim "index or primary key cannot contain
a Null value".
a string of spaces is clearly not a null value.
am i missing something ?
is there some option to control this behaviour?

any help greatly appreciated

Nov 13 '05 #4

shallow

"Rick Brandt" <ri*********@hotmail.com> wrote in message news:<2l************@uni-berlin.de>...

"shallow" <de*********@yahoo.com> wrote in message
news:e3*************************@posting.google.co m...
.... What you're missing is that through the standard user interface Access
automatically trims all trailing spaces. In the case of an entry with
nothing but spaces all of it is trimmed.

Why on earth would you want a PK value of blanks?
mapping legacy data in this case and the field in question is not the
only component in the primary key.
to me, there's not much difference in a string of spaces and a
"human-readable" string, apart from some space wasteage.
the primary keys can be unique anyway.
I suppose if you created
the record with an append query the value might take.

the record is created via "select * from table where 1<0".
i append data via addnew.
msaccess does not really like it.

as a workaround, i introduced another mapping from space-strings to
something non-whitespace.

why are "x" and "xxx" allowed values, but not " " and " "?
zapping trailing spaces is a pretty convenient thing, but it would be
nice to have a way of switching it off...

Nov 13 '05 #5

david epsom dot com dot au

> the record is created via "select * from table where 1<0".

i append data via addnew.
If you used an append query, this would not be a problem.

(david)

"shallow" <de*********@yahoo.com> wrote in message
news:e3**************************@posting.google.c om... "Rick Brandt" <ri*********@hotmail.com> wrote in message

news:<2l************@uni-berlin.de>...

"shallow" <de*********@yahoo.com> wrote in message
news:e3*************************@posting.google.co m...

...

What you're missing is that through the standard user interface Access
automatically trims all trailing spaces. In the case of an entry with
nothing but spaces all of it is trimmed.

Why on earth would you want a PK value of blanks?

mapping legacy data in this case and the field in question is not the
only component in the primary key.
to me, there's not much difference in a string of spaces and a
"human-readable" string, apart from some space wasteage.
the primary keys can be unique anyway.

I suppose if you created
the record with an append query the value might take.

the record is created via "select * from table where 1<0".
i append data via addnew.
msaccess does not really like it.

as a workaround, i introduced another mapping from space-strings to
something non-whitespace.

why are "x" and "xxx" allowed values, but not " " and " "?
zapping trailing spaces is a pretty convenient thing, but it would be
nice to have a way of switching it off...

Nov 13 '05 #6

Rick Brandt

"shallow" <de*********@yahoo.com> wrote in message
news:e3**************************@posting.google.c om...

"Rick Brandt" <ri*********@hotmail.com> wrote in message

news:<2l************@uni-berlin.de>...

"shallow" <de*********@yahoo.com> wrote in message
news:e3*************************@posting.google.co m...

...

What you're missing is that through the standard user interface Access
automatically trims all trailing spaces. In the case of an entry with
nothing but spaces all of it is trimmed.

Why on earth would you want a PK value of blanks?

mapping legacy data in this case and the field in question is not the
only component in the primary key.
to me, there's not much difference in a string of spaces and a
"human-readable" string, apart from some space wasteage.
the primary keys can be unique anyway.

The database purists would tell you that if the field sometimes has no data
then it is not a viable PK candidate. Better to just use a surrogate PK
like an AutoNumber and then use a Unique Index on these fields to prevent
duplicates. Unique indexes (unlike PKs) can be set up to allow Nulls in
the fields.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #7