Inserting tabs (or only spaces) in Datasheet view

[Guest]

I'm entering data in my table via datasheet view. In some text fields, I
want (for instance) three or four spaces, nothing else. Spaces are important
to me. However, as soon as I hit enter or tab or an arrow key, Access
changes my spaces to an empty cell. How do I combat this? Also, how would I
enter a tab?

I know ctrl+enter puts a carriage return in a text field, but ctrl (or
shift) + tab (or space) do not do the trick for me.

Thanks for any help!

 

[Jeff Boyce]

If my form shows you a control that seems to have nothing in it, does it
have spaces or a zero-length string, or does it have nothing (Null) for a
value?

You've decided that you need spaces for some purpose (undisclosed), but how
are your users to make sense of what you would have Access store?

If you describe a bit more about why you feel you need spaces, the 'group
readers may be able to offer an alternative approach...

Regards

Jeff Boyce
<Office/Access MVP>

 

[John Vinson]

I'm entering data in my table via datasheet view. In some text fields, I
want (for instance) three or four spaces, nothing else. Spaces are important
to me. However, as soon as I hit enter or tab or an arrow key, Access
changes my spaces to an empty cell. How do I combat this? Also, how would I
enter a tab?

I know ctrl+enter puts a carriage return in a text field, but ctrl (or
shift) + tab (or space) do not do the trick for me.

Thanks for any help!

Access trims trailing blanks from all text fields, and if this leaves
nothing, it stores either a NULL or a zero-length string (depending on
the properties of the field).

As Jeff asks... What is the difference between the values

and

for the user?


John W. Vinson[MVP]

 

[Guest]

There are no users, it is an internal application for creating MS Word
documents (in XML format) on the fly. My templates are a little too complex
to use databinding .aspx pages, so I am storing my template pieces in an SQL
database... and using Access to easily edit fields at will in a trial and
error fashion. For instance (and this is a very simple example), let's
assume I run a master sql statement which selects fields from a table:
employee. I then see which template is needed for this document and select
the proper rows from a template table, which contains: fieldname, preXML,
postXML, and order.

Let's say that returns the following:

PreXML Field PostXML Order
'<w:t>' First Name ' - ' 1
'' Last Name '' 2
' ' Location '</w:t> 3


This gives me a text run with the employee's first - last location.
And, yes, whitespace is very important to me here, otherwise I have a first
-last with the '-' butting right up against the last name.

I can get the whitespace in there with SQL Query Analyzer, but that is a
rather large pasin when I am dealing with as many template pieces as I am,
constantly changing them as I develop. Access provides an easier interface
than SQL Server Enterprise Manager... especially with respect to
subdatasheets, but falls short when I need important whitespace and tabs.

Maybe a little too much info, but ya'll wanted to know why I needed
whitespace. Trust me, I do.

Thanks!

 

[Guest]

ooop. 'pasin' = 'pain'

 

[John Vinson]

I am storing my template pieces in an SQL database...

In SQL you can use a Char() field rather than a Varchar to store fixed
length.

Or, of course, you can append blanks in code without storing them in
the field - [Firstname] & ' ' & [lastname] for example.

John W. Vinson[MVP]

 

[Guest]

No. Not knowing the application, it would be difficult for you to
understand. Hardcoding a ' ' between fields would demolish the whole reason
why I am storing templates in a database to begin with. Nothing within the
template should be hardcoded. These things need to change at my boss' whim,
without having to recompile each and every time. "Hey... I don't like the
spacing here... add another space", or "Tomorrow, I want a book with sections
9 and 16 having dashes between fields 6 and 7, but tabs between those same
fields in the remaining sections... and then after tomorrow, change that
style back to what it was." etc, etc. I'm going to be having hundreds of
styles that will be responsible for spitting out Word documents in excess of
10,000 pages each. And the place where I am storing the XML template pieces
have to be text fields, because some of them are pages and pages long (ever
seen the XML behind a Word document?).

What I wanted from Access was an "ease of use" functionality to speed up
development. I guess Access will simply not do it. Kinda sad, in my
opinion, that you can't just type in a space or a tab within a datasheet view
and have it save to the database. Instead, I have to go to SQL Query
analyzer, find the index for each row I want to update, and type something
akin to: "update StyleXML set SXMPreXML = ' ' where SxmStyleFieldID in
(232,235,238,221,241,34,36,39,100,234,744,3331)"

The "automatic" trimming of trailing spaces that you mention might be a
great feature to most people, but it hinders others. It's like Microsoft is
figuring it knows what is better for a coder than the coder does. It should
at least be a feature you can toggle on or off. Oh well.

Thanks for your time, though!

I am storing my template pieces in an SQL database...

In SQL you can use a Char() field rather than a Varchar to store fixed
length.

Or, of course, you can append blanks in code without storing them in
the field - [Firstname] & ' ' & [lastname] for example.

John W. Vinson[MVP]

 

[Jeff Boyce]

You may have already tried this, so ...

If you save just the characters (and not the spaces you're using to ?pad out
the field for distribution), you could export from a query, rather than the
table. And your query could include something like:

Spaced: [YourField] & Space(30-Len([YourField]))

If you don't want to hardcode the "30", you could create a function that
does the same, and uses a variable for the "total length of field"
calculation.

Or perhaps I still don't get it...

Regards

Jeff Boyce
<Office/Access MVP>

No. Not knowing the application, it would be difficult for you to
understand. Hardcoding a ' ' between fields would demolish the whole
reason
why I am storing templates in a database to begin with. Nothing within
the
template should be hardcoded. These things need to change at my boss'
whim,
without having to recompile each and every time. "Hey... I don't like the
spacing here... add another space", or "Tomorrow, I want a book with
sections
9 and 16 having dashes between fields 6 and 7, but tabs between those same
fields in the remaining sections... and then after tomorrow, change that
style back to what it was." etc, etc. I'm going to be having hundreds of
styles that will be responsible for spitting out Word documents in excess
of
10,000 pages each. And the place where I am storing the XML template
pieces
have to be text fields, because some of them are pages and pages long
(ever
seen the XML behind a Word document?).

What I wanted from Access was an "ease of use" functionality to speed up
development. I guess Access will simply not do it. Kinda sad, in my
opinion, that you can't just type in a space or a tab within a datasheet
view
and have it save to the database. Instead, I have to go to SQL Query
analyzer, find the index for each row I want to update, and type something
akin to: "update StyleXML set SXMPreXML = ' ' where SxmStyleFieldID in
(232,235,238,221,241,34,36,39,100,234,744,3331)"

The "automatic" trimming of trailing spaces that you mention might be a
great feature to most people, but it hinders others. It's like Microsoft
is
figuring it knows what is better for a coder than the coder does. It
should
at least be a feature you can toggle on or off. Oh well.

Thanks for your time, though!

I am storing my template pieces in an SQL database...

In SQL you can use a Char() field rather than a Varchar to store fixed
length.

Or, of course, you can append blanks in code without storing them in
the field - [Firstname] & ' ' & [lastname] for example.

John W. Vinson[MVP]

 

[Guest]

That is a very interesting suggestion, and might almost work... but trying
to look to the future flexibility and maintainability of this beast, I'm not
so sure. By future flexibility, I mean someone might request that a certain
field have 2 tabs, 3 spaces, and a carriage return after or before it... or
any other such combination. By future maintainability, I mean that nobody in
the future will be getting at the VB code behind all this... but will
probably be trained to alter/create templates. Thus, I would have to add a
field at the end of the table "AppendWhitespaceType" (Carraige return, space
or tab) and a field for Quantity. Then, I could code it to do what you are
suggesting above, based on what values are in the database for a particular
field, but I don't know if it would be easy to still provide the flexibility
I describe above. Hmmm... I'd really have to add a whole new table, allowing
multiple entries for WhiteSpaceType, Quantity, and Order... such that any
type and combination of whitespace could be added without messing with the
VB.NET code. However, training someone to keep up with that logic in an
external table would be about as easy as showing them how to insert
whitespace in via SQL Query Analyzer as I posted in my last reply.

And I thought Access was easy.

You may have already tried this, so ...

If you save just the characters (and not the spaces you're using to ?pad out
the field for distribution), you could export from a query, rather than the
table. And your query could include something like:

Spaced: [YourField] & Space(30-Len([YourField]))

If you don't want to hardcode the "30", you could create a function that
does the same, and uses a variable for the "total length of field"
calculation.

Or perhaps I still don't get it...

Regards

Jeff Boyce
<Office/Access MVP>

No. Not knowing the application, it would be difficult for you to
understand. Hardcoding a ' ' between fields would demolish the whole
reason
why I am storing templates in a database to begin with. Nothing within
the
template should be hardcoded. These things need to change at my boss'
whim,
without having to recompile each and every time. "Hey... I don't like the
spacing here... add another space", or "Tomorrow, I want a book with
sections
9 and 16 having dashes between fields 6 and 7, but tabs between those same
fields in the remaining sections... and then after tomorrow, change that
style back to what it was." etc, etc. I'm going to be having hundreds of
styles that will be responsible for spitting out Word documents in excess
of
10,000 pages each. And the place where I am storing the XML template
pieces
have to be text fields, because some of them are pages and pages long
(ever
seen the XML behind a Word document?).

What I wanted from Access was an "ease of use" functionality to speed up
development. I guess Access will simply not do it. Kinda sad, in my
opinion, that you can't just type in a space or a tab within a datasheet
view
and have it save to the database. Instead, I have to go to SQL Query
analyzer, find the index for each row I want to update, and type something
akin to: "update StyleXML set SXMPreXML = ' ' where SxmStyleFieldID in
(232,235,238,221,241,34,36,39,100,234,744,3331)"

The "automatic" trimming of trailing spaces that you mention might be a
great feature to most people, but it hinders others. It's like Microsoft
is
figuring it knows what is better for a coder than the coder does. It
should
at least be a feature you can toggle on or off. Oh well.

Thanks for your time, though!

On Mon, 21 Nov 2005 05:51:09 -0800, "XjeaxaxX"

I am storing my template pieces in an SQL database...

In SQL you can use a Char() field rather than a Varchar to store fixed
length.

Or, of course, you can append blanks in code without storing them in
the field - [Firstname] & ' ' & [lastname] for example.

John W. Vinson[MVP]

 

[John Vinson]

The "automatic" trimming of trailing spaces that you mention might be a
great feature to most people, but it hinders others. It's like Microsoft is
figuring it knows what is better for a coder than the coder does. It should
at least be a feature you can toggle on or off. Oh well.

Agreed!

The only thing I can suggest is storing a "trailer" character, ~ or
something, at the end of the string and trimming it off in your code.


John W. Vinson[MVP]

 

[Guest]

Now that's the best idea I've heard all day!

Is there a good place to offer suggestions/request for future versions of
Access? If so, I would like to request:

1) trimming trailing whitespace be an option you can turn on/off
2) Add a way to enter a tab. Much like carriage return is "Ctrl + Enter",
"Ctrl + Tab" (or some such sequence) should put a tab character in. As is, I
have to copy and paste from notepad... yuck.

Thanks!

 

[Jeff Boyce]

I'm pretty sure the subject's already been raised (upstring), but if the
eventual user of the data could have any/all variations on what s/he wants,
another approach might be to put the determination of formatting closer to
the user, rather than deeper in Access. It seems you're describing not so
much a data storage issue as a formatting/display issue.

Is there a chance you can keep your data "clean", and do the formatting in
the eventual destination?

Regards

Jeff Boyce
<Office/Access MVP>

That is a very interesting suggestion, and might almost work... but trying
to look to the future flexibility and maintainability of this beast, I'm
not
so sure. By future flexibility, I mean someone might request that a
certain
field have 2 tabs, 3 spaces, and a carriage return after or before it...
or
any other such combination. By future maintainability, I mean that nobody
in
the future will be getting at the VB code behind all this... but will
probably be trained to alter/create templates. Thus, I would have to add
a
field at the end of the table "AppendWhitespaceType" (Carraige return,
space
or tab) and a field for Quantity. Then, I could code it to do what you
are
suggesting above, based on what values are in the database for a
particular
field, but I don't know if it would be easy to still provide the
flexibility
I describe above. Hmmm... I'd really have to add a whole new table,
allowing
multiple entries for WhiteSpaceType, Quantity, and Order... such that any
type and combination of whitespace could be added without messing with the
VB.NET code. However, training someone to keep up with that logic in an
external table would be about as easy as showing them how to insert
whitespace in via SQL Query Analyzer as I posted in my last reply.

And I thought Access was easy.

You may have already tried this, so ...

If you save just the characters (and not the spaces you're using to ?pad
out
the field for distribution), you could export from a query, rather than
the
table. And your query could include something like:

Spaced: [YourField] & Space(30-Len([YourField]))

If you don't want to hardcode the "30", you could create a function that
does the same, and uses a variable for the "total length of field"
calculation.

Or perhaps I still don't get it...

Regards

Jeff Boyce
<Office/Access MVP>

No. Not knowing the application, it would be difficult for you to
understand. Hardcoding a ' ' between fields would demolish the whole
reason
why I am storing templates in a database to begin with. Nothing within
the
template should be hardcoded. These things need to change at my boss'
whim,
without having to recompile each and every time. "Hey... I don't like
the
spacing here... add another space", or "Tomorrow, I want a book with
sections
9 and 16 having dashes between fields 6 and 7, but tabs between those
same
fields in the remaining sections... and then after tomorrow, change
that
style back to what it was." etc, etc. I'm going to be having hundreds
of
styles that will be responsible for spitting out Word documents in
excess
of
10,000 pages each. And the place where I am storing the XML template
pieces
have to be text fields, because some of them are pages and pages long
(ever
seen the XML behind a Word document?).

What I wanted from Access was an "ease of use" functionality to speed
up
development. I guess Access will simply not do it. Kinda sad, in my
opinion, that you can't just type in a space or a tab within a
datasheet
view
and have it save to the database. Instead, I have to go to SQL Query
analyzer, find the index for each row I want to update, and type
something
akin to: "update StyleXML set SXMPreXML = ' ' where SxmStyleFieldID
in
(232,235,238,221,241,34,36,39,100,234,744,3331)"

The "automatic" trimming of trailing spaces that you mention might be a
great feature to most people, but it hinders others. It's like
Microsoft
is
figuring it knows what is better for a coder than the coder does. It
should
at least be a feature you can toggle on or off. Oh well.

Thanks for your time, though!



:

On Mon, 21 Nov 2005 05:51:09 -0800, "XjeaxaxX"

I am storing my template pieces in an SQL database...

In SQL you can use a Char() field rather than a Varchar to store fixed
length.

Or, of course, you can append blanks in code without storing them in
the field - [Firstname] & ' ' & [lastname] for example.

John W. Vinson[MVP]