By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,963 Members | 1,778 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,963 IT Pros & Developers. It's quick & easy.

whitespace string as primary key

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
"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

P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
> 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

P: n/a
"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

This discussion thread is closed

Replies have been disabled for this discussion.