Access is truncating my strings with trailing spaces

tommy

Hi all,

I' m adding strings to some fields in my table via Access. The strings
sometimes have trailing spaces and I really need to have it that way,
but Access truncates trailing spaces. How can I force Access not to
truncate?!

Thanx,

/Toommy

Apr 26 '06 #1

Rick Brandt

<to***@infografix.nu> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...

Hi all,

I' m adding strings to some fields in my table via Access. The strings
sometimes have trailing spaces and I really need to have it that way,
but Access truncates trailing spaces. How can I force Access not to
truncate?!

Through the GUI I don't think you can. Data with trailing spaces inserted via
queries will retain them, but AFAIK you cannot do this from the keyboard.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Apr 26 '06 #2

David W. Fenton

to***@infografix.nu wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:

I' m adding strings to some fields in my table via Access. The
strings sometimes have trailing spaces and I really need to have
it that way, but Access truncates trailing spaces. How can I force
Access not to truncate?!

I personally can't think of a reason to store trailing spaces.

1. if you're storing data for HTML, any white space is rendered as a
single space, so at most you'd be losing one space.

2. in any context, it's easier to pad to the required length when
you retrieve data.

3. if you need the trailing spaces for a fixed-width export, then
that's easily handled with an export spec that is set to fixed-width
(and will pad the output accordingly).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Apr 26 '06 #3

scoots987

How about when we want to do an incremental search in a text box that
will contain spaces? How do we include the spaces?

So if I have a textbox's OnChange event firing the follow code:

Dim pvPos As Integer
Dim pvSQLString As String

DoCmd.Hourglass True
Text20.SetFocus
pvSQLString = Text20.Text
pvPos = Len(Text20.Text)
pvSQLString = "select bdas.* from bdas where bdas.ASNAME Like '" & _
pvSQLString & "*'"
Me.RecordSource = pvSQLString
Text20.SetFocus
Me.Text20.SelStart = pvPos
DoCmd.Hourglass False

How do I grab the one space the user has typed to include in the
Select? If I am trying to grab "The fox jumped over...", how do I
include in the Select syntax "like 'The f*'"

Thanks for any help.

Scott

David W. Fenton wrote:

to***@infografix.nu wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:

I' m adding strings to some fields in my table via Access. The
strings sometimes have trailing spaces and I really need to have
it that way, but Access truncates trailing spaces. How can I force
Access not to truncate?!

I personally can't think of a reason to store trailing spaces.

1. if you're storing data for HTML, any white space is rendered as a
single space, so at most you'd be losing one space.

2. in any context, it's easier to pad to the required length when
you retrieve data.

3. if you need the trailing spaces for a fixed-width export, then
that's easily handled with an export spec that is set to fixed-width
(and will pad the output accordingly).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Apr 26 '06 #4

Bob Quintal

sc*******@gmail.com wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

How about when we want to do an incremental search in a text
box that will contain spaces? How do we include the spaces?

So if I have a textbox's OnChange event firing the follow
code:

Dim pvPos As Integer
Dim pvSQLString As String

DoCmd.Hourglass True
Text20.SetFocus
pvSQLString = Text20.Text
pvPos = Len(Text20.Text)
pvSQLString = "select bdas.* from bdas where bdas.ASNAME Like
'" & _
pvSQLString & "*'"
Me.RecordSource = pvSQLString
Text20.SetFocus
Me.Text20.SelStart = pvPos
DoCmd.Hourglass False

How do I grab the one space the user has typed to include in
the Select? If I am trying to grab "The fox jumped over...",
how do I include in the Select syntax "like 'The f*'"

Thanks for any help.

Scott
A couple of points to help your understanding.

1) The text property only exists when the control has focus and
contains the keystrokes presented by the user. This includes
spaces, etc.
2) The control's value property only changes when the entry gets
updated (i'm not sure if the change is done when the before
update event fires, but definitely by the firing of the
afterupdate event. It's at the time of comitting the .text to
the .value that the trailing spaces get trimmed.

In your code, you are changing the recordsource in order to
implement a filter. You would have faster code if you simply set
the formname.filter to the where clause and the filterOn
property to true.


David W. Fenton wrote:

to***@infografix.nu wrote in
news:11*********************@v46g2000cwv.googlegro ups.com:

> I' m adding strings to some fields in my table via Access.
> The strings sometimes have trailing spaces and I really
> need to have it that way, but Access truncates trailing
> spaces. How can I force Access not to truncate?!

I personally can't think of a reason to store trailing
spaces.

1. if you're storing data for HTML, any white space is
rendered as a single space, so at most you'd be losing one
space.

2. in any context, it's easier to pad to the required length
when you retrieve data.

3. if you need the trailing spaces for a fixed-width export,
then that's easily handled with an export spec that is set to
fixed-width (and will pad the output accordingly).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

--
Bob Quintal

PA is y I've altered my email address.

Apr 26 '06 #5

David W. Fenton

sc*******@gmail.com wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

How about when we want to do an incremental search in a text box
that will contain spaces? How do we include the spaces?

I have implemented any number of such searches and never once had a
client ask for the ability to put in trailing spaces. Nor have I
ever seen a situation where it was needed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Apr 27 '06 #6

Lyle Fairfield

sc*******@gmail.com wrote in news:1146077454.551345.239350
@u72g2000cwu.googlegroups.com:

How do I grab the one space the user has typed to include in the
Select? If I am trying to grab "The fox jumped over...", how do I
include in the Select syntax "like 'The f*'"

Not for the faint of heart but I believe this might work:

Change the datatype of the field (assuming there is a field behind the text
box; if not them make one) to binary. TTBOMK this has to be done with SQL
as in
CurrentProject.Connection.Execute "ALTER TABLE TabelName ALTER FieldName
Binary"
Then use the SetFocus Method and the .Text Property as you already are. In
a very few tests this returned the trailing spaces for me. But .Value
returned a string padded to the maximum length of the field.

Before doing this, you would need to assess whether or not Binary screwed
up something else and as always, test it on some throwaway copy.

--
Lyle Fairfield

Apr 27 '06 #7

david epsom dot com dot au

I think the input mask may help, but normally you
do it by unbinding the control, and capturing the text instead.

(david)

<to***@infografix.nu> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...

Hi all,

I' m adding strings to some fields in my table via Access. The strings
sometimes have trailing spaces and I really need to have it that way,
but Access truncates trailing spaces. How can I force Access not to
truncate?!

Thanx,

/Toommy

Apr 27 '06 #8

scoots987

Thanks for your response. I did try a filter but am having the same
problem. This is where I am right now. How do I capture the trailing
space to include into the select or filter? The textbox is unbound.

Thanks!

May 2 '06 #9

scoots987

David, please forgive me. Am I to understand your post to saying
because you have never had to such a task, it is not necessary?

Thanks!

May 2 '06 #10

scoots987

Lyle, thanks for your post. I can not modify the table for this.
Besides the textbox is unbound. Do I understand your point?

Thanks!

May 2 '06 #11

scoots987

David, that is what I am doing. The textbox is unbound. My problem is
that the value gets trimmed of trailing spaces before I can grab it.
And to allow the user to type a sentence for this search.

I would like to get this to work through a filter.

Thanks!

May 2 '06 #12

David W. Fenton

sc*******@gmail.com wrote in
news:11**********************@j73g2000cwa.googlegr oups.com:

David, please forgive me. Am I to understand your post to saying
because you have never had to such a task, it is not necessary?

No. I'm saying that in 10 years of regular Access application
development I've never encountered a circumstance where I needed
trailing spaces for any purpose. You provided an example (searching
for a trailing space) that I'd never thought of because none of the
search interfaces I create work in that manner. Even yours ought to
handle "search string *" if you want the space to be included.
Presuming that you're appending a "*" already, it's not really an
issue, as the double-up "**" causes no problem with the LIKE search.

Your situation is not the kind of problem I'd worry about. My users
have a hard enough time even *seeing* spaces onscreen, let alone
using them in searching.

I would also note that I know of no search interface that honors
trailing spaces without some special characters added. Google won't
do it even if you use quotes. Since Google is the most familiar
search interface on the planet, I'd suggest you use it as your
model.

That doesn't preclude allowing a special method for searching for
the trailing space, but trying to code for handling it by default
seems like way too much work for a requirement that virtually no one
actually has.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

May 2 '06 #13

Bob Quintal

sc*******@gmail.com wrote in
news:11**********************@e56g2000cwe.googlegr oups.com:

Thanks for your response. I did try a filter but am having the
same problem. This is where I am right now. How do I capture
the trailing space to include into the select or filter? The
textbox is unbound.

Thanks!

The issue is one of filtering versus filtering.

When someone sets a filter -> like "King" & "*"
or like "King " & "*" , the same record will show up at the top of
the list. I say again "THE SAME RECORD"

why make a big fuss about the space?
--
Bob Quintal

PA is y I've altered my email address.

May 2 '06 #14

Bob Quintal

sc*******@gmail.com wrote in
news:11**********************@e56g2000cwe.googlegr oups.com:

Thanks for your response. I did try a filter but am having the
same problem. This is where I am right now. How do I capture
the trailing space to include into the select or filter? The
textbox is unbound.

Thanks!

having asked the important question in my other response, in order
to preserve the trailing space is to save the textbox.text to a
memory variable in the beforeupdate event for that control and use
the variable in creating your filter's where clause.
--
Bob Quintal

PA is y I've altered my email address.

May 2 '06 #15

david epsom dot com dot au

You would have to capture the keydown or keypress

<sc*******@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...

David, that is what I am doing. The textbox is unbound. My problem is
that the value gets trimmed of trailing spaces before I can grab it.
And to allow the user to type a sentence for this search.

I would like to get this to work through a filter.

Thanks!

May 3 '06 #16

scoots987

Thanks Bob, I think I am making headway here. What about updating the
textbox to contain the space and waiting for the next key. My current
problem now is that as soon as the OnChange event fires, the space is
trimmed so the when user comes to type the next character, the space is
gone and cannot continue typing the sentence with spaces.

I tried to update the textbox with the "memory variable" from the
BeforeUpdate event but now it appears that the OnChange events fires
repeatedly a few times. The space never does remain. Anyway to "turn
off" the OnChange event until the process has completed?

My code now looks like:
Dim pvStoredString As String
Dim pvPos As Integer

Private Sub Text20_BeforeUpdate(Cancel As Integer)
pvStoredString = Text20.Text
pvPos = Len(Text20.Text)
End Sub

Private Sub Text20_Change()
Dim pvSQLString As String

DoCmd.Hourglass True
Text20.SetFocus
pvSQLString = "select bdnew_bdas.* from bdnew_bdas where
bdnew_bdas.ASNAME Like '" & _
pvStoredString & "*'"
Me.RecordSource = pvSQLString
Text20.SetFocus
Text20.Text = pvStoredString
Me.Text20.SelStart = pvPos
DoCmd.Hourglass False
End Sub

Any suggestions?

Thanks!

May 3 '06 #17

Bob Quintal

sc*******@gmail.com wrote in
news:11**********************@j73g2000cwa.googlegr oups.com:

Thanks Bob, I think I am making headway here. What about
updating the textbox to contain the space and waiting for the
next key. My current problem now is that as soon as the
OnChange event fires, the space is trimmed so the when user
comes to type the next character, the space is gone and cannot
continue typing the sentence with spaces.
I tried to update the textbox with the "memory variable" from
the BeforeUpdate event but now it appears that the OnChange
events fires repeatedly a few times. The space never does
remain. Anyway to "turn off" the OnChange event until the
process has completed?

My code now looks like:
Dim pvStoredString As String
Dim pvPos As Integer

Private Sub Text20_BeforeUpdate(Cancel As Integer)
pvStoredString = Text20.Text
pvPos = Len(Text20.Text)
End Sub

Private Sub Text20_Change()
Dim pvSQLString As String

DoCmd.Hourglass True
Text20.SetFocus
pvSQLString = "select bdnew_bdas.* from bdnew_bdas where
bdnew_bdas.ASNAME Like '" & _
pvStoredString & "*'"
Me.RecordSource = pvSQLString
Text20.SetFocus
Text20.Text = pvStoredString
Me.Text20.SelStart = pvPos
DoCmd.Hourglass False
End Sub

Any suggestions?

Thanks!

Why use the OnChange event? Use the BeforeUpdate and AfterUpdate
events.

changing the recordsource in the Change event is going to be
slow.

Delete the entire Text20_Change() sub

Add this:
Private Sub text20_AfterUpdate()
me.filter="ASNAME Like '" pvStoredString & "*'"
me.filterOn = true
End sub

--
Bob Quintal

PA is y I've altered my email address.

May 3 '06 #18