Blanks at end of fields

D

Dom Olivastro

Simple question. I noticed that I can't place a blank at
the end of a text field in access. For example, I can't
have "DOM " instead of "DOM".

Is there any reason for this? Any way around it?
 
A

Albert D. Kallal

The forms editor kindly removes those blanks (a good thing, as you imagine
the mess it would be when you try and go:


select * from tblCustomes where City = 'Edmonton'

Of course, any extra spaces would mess up the above, and anyone who accident
enters a extra space while entering the city would fail in the above search.

Of all the commercial database products I used in the last 20 years, I
cannot recall ONE system that would leave trailing blanks during data entry
(dbase was fixed length...so trailing blanks don't apply in concept). So,
looking at 20 years worth of computer products and looking a virtually every
commercial database in use today on the planet, I would have to say that
most editors remove those extra blanks during form entry.

Ironically, Excel does not (I very curious as to why this is the case!!).

However, you can certainly insert and STORE extra blanks after some data in
a field, but I can't imagine why you would want to do this?

You might want to try and explain why you need to save the extra spaces? I
am sure there are plenty solutions to your problem (after all the whole
industry for 20 years has had to deal with this issue, and decided that NOT
having training blanks is the way to go!). So, I don't think "storing" those
trailing blanks is your solution, but then again, I could be proven wrong.

Why do you need the trailing blanks? (or, perhaps you were just curious?)
 
F

fredg

Simple question. I noticed that I can't place a blank at
the end of a text field in access. For example, I can't
have "DOM " instead of "DOM".

Is there any reason for this? Any way around it?
Click to expand...

That's the way it is.
If you have a specific need for a blank space let us know why. Perhaps
someone can think of a work-around.
 
G

Guest

SQL Server allows trailing blanks at the end of the field,
and I believe SQL Server is the industry standard nowadays.

Here is my need for trailing blanks.

I have a table with two fields: "Code"
and "Replacement". The Code field may have "'" and
the corresponding replacement field has an apostrophe
("'"). The table is used to go through text and make
replacements.

For example if the text has:
"It's here&semicol; now go home"

It should be translated to:
"It's here; now go home"

But sometimes the replacement is just a blank (eg, the
replacement for "&HBlank;" should be " ") and sometimes it
ends with a blank (eg, the replacement for ":"
should be ": "). We use a table so that we can change the
replacements or add codes at any given time. If we
translate for a German customer, the replacement
for "lquote;" becomes ",,", and so on.
 
A

Albert D. Kallal

SQL Server allows trailing blanks at the end of the field,
and I believe SQL Server is the industry standard nowadays.
Click to expand...

Yes, and you can also store blanks in a JET table also.

However, you don't want to confuse ms-access with the database engine?
ms-access is just the editor here, or a tool that connects to YOUR
database of choice. (you can use ms-access with sql server for example).

You can use ms-access with sql server, with Oracle, or in our case with JET.
All of these database engines can store blanks, but when you
build a form and "edit" the data, then the trailing blanks are removed. JET,
ms-sql server, or Oracle do NOT remove the training blanks.

So, you can safely store trailing blanks..but the screen editor removes
them....

You can safely replace, insert and save blanks to a table. To further
clarify, only trailing blanks are removed by the SCREEN editor, and not the
data engine!

If you enter into the screen editor:

Hello how are you

The blanks are kept intact. however, if you enter

Hello how are you _______

The "_______" in this example is assumed to be trailing blanks...they will
be removed when you hit enter(or tab) on the screen

However, noting is stopping you from using sql update statements or code to
insert blanks into the data and save it.
Here is my need for trailing blanks.
Click to expand...
I have a table with two fields: "Code"
and "Replacement". The Code field may have "'" and
the corresponding replacement field has an apostrophe
("'"). The table is used to go through text and make
replacements.
Click to expand...

What a great example you have! (I am serous!)

Ok, my suggestion here would be to code an exception

I assume you now have:

Code Replacement
&apos '
&aExclam !
&HBlank Blank

You code then would have to take the word "blank" and subsite a space ( " ")

Furhter, you COULD force the issue, and have all data surrounded by quotes,
and thus during data entry
you could enter:

Code Replacement
&apos "'"
&aExclam "!"
&HBlank " "

In the above case, then during data entry, quotes would be entered around
whatever you enter. As mentioned, there is certainly no problem with leading
blanks, or blanks entering in the middle...it is only trailing blanks that
are removed here (as most screen editors have always removed them).

I would also make sure you turn off "allow autocorrect" in the other tab,
least you have the office "auto correct" try and fix/change what you type
in!
(you will find this setting in the "other" tab of the text box control when
designing a form).

So, if your design is such now that quotes are entered, then the blanks in
the prefix, or middle of the string will remain.

It is only as mentioned the trailing blanks that get removed. If your entry
stuff is being changed, then I would check the tools->autocorrect options
(I usually turn this stuff off!!).
 
D

Dom

I ended up using a tilde (~) instead of trailing blanks.
There really should be a property
like "AllowTrailingBlanks", to make everything much easier.

Thanks for all your help.
Dom
 
R

Rick Brandt

Dom said:
I ended up using a tilde (~) instead of trailing blanks.
There really should be a property
like "AllowTrailingBlanks", to make everything much easier.
Click to expand...

I don't remember the details but you can create a table in code (not with
the GUI) and define a text field as a Char instead of a VarChar. Then the
field will always have padded spaces out to the full length of the field.
Of course if you want to have trailing blanks only some of the time that
doesn't help you.
 
Top