User Entry of Spaces in Fields

[David A Peterson]

I have an Access 2000 database built in which I must
periodically enter one or more spaces into a given field.
I have not been able to figure out a way to do this
interactively, and must always build an update query to
accomplish the task. A real nuisance.

I have tried every combination of settings in the "default
value", "required" and "allow zero length" fields I can
think of, but it still doesn't work.

Any suggestions?

Thanks,
Dave

 

[TC]

Remember that Access supplies the forms, reports etc, and Jet (the
underlying database engine) supports the tables & queries.

Jet has no objection to storing spaces in fields. However, Access decides
that this doesn't make sense, so whenever you enter data into a textbox on a
form, Access strips all trailing spaces before it passes the value to Jet.
If your value is *all* spaces, then, stripping trailing spaces effectively
changes the value to Null. This happens regardless of what settings you have
defined for the table. Those settings only come into play when Access has
decided what value it wants Jet to store in the table.

Having said all that, it is a baaaad idea to store spaces in fields! It
makes diagnosis of problems very difficult. You do an adhoc-query or open a
datasheet to check some data, & you see a column of blank values. Are those
values really blanks? or Nulls? or empty (zero-length) strings? or an
unknown mixture of all three?

So, *why* do you want to store blanks? There must be a better way to achive
what you actually need to achieve.

HTH,
TC

 

[Joe Fallon]

I think you solved it. <g>

In other words, there may not be a way to do it interactively and an Update
query is the best solution.

 

[Jeff Boyce]

David

I'm with TC on this issue -- what is it that you want to accomplish by
having spaces in fields in Access? In other words, "why do you want to do
this?", not "how can I do this?" What is your underlying business need?

More info, please...

Jeff Boyce
<Access MVP>