Deleting extra space at data entry time

[ChelleV]

A user has an Access table that they paste information in
from another program (I am not familiar with any other
background info).

When they paste, often there is a double space showing up
in the text which is causing problems.

My question: can I write a macro or something that will
eliminate any and all double spaces upon entry into the
Access table, or at least once the row loses focus? They
are entering directly to the table, not using forms.

Thank you for your time and ideas.

ChelleV

 

[John Nurick]

Hi ChelleV,

No, you can't do this in an Access table.

One solution would be to write a query that eliminates the double spaces
and have the user run this after they finish pasting in a batch of
records. Alternatively, use a form and adjust the text in its
BeforeUpdate event procedure.

Without background information that's about all one can say.

 

[ChelleV]

Ok, they have agreed to add a form to enter the data, so
that we have more options. It will be a very basic form
simply for data entry.

How would I do eliminate the double spaces with the
BeforeUpdate event? We prefer to use macros for this
specific problem, but if it can only be done with Visual
Basic, I can do that as well.

Thanks again,
ChelleV

-----Original Message-----
Hi ChelleV,

No, you can't do this in an Access table.

One solution would be to write a query that eliminates the double spaces
and have the user run this after they finish pasting in a batch of
records. Alternatively, use a form and adjust the text in its
BeforeUpdate event procedure.

Without background information that's about all one can say.

A user has an Access table that they paste information in
from another program (I am not familiar with any other
background info).

When they paste, often there is a double space showing up
in the text which is causing problems.

My question: can I write a macro or something that will
eliminate any and all double spaces upon entry into the
Access table, or at least once the row loses focus? They
are entering directly to the table, not using forms.

Thank you for your time and ideas.

ChelleV

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Guest]

One more question. I've been working with the Update
Query route. I can get the rows I want by adding this
criteria:
Like "* *" And Not Like "*. *"

However, how can I write the UPDATE TO line? When I put
in "* *" it's obviously replacing the line with * *.

Thanks again,
ChelleV

 

[John Nurick]

Assuming you're using Access 2000 or later, use something like this in
the control's or form's BeforeUpdate event procedure. Must be VBA, can't
be a macro. Repeat for each control in which you want the replacement to
happen.

With Me.NameOfControl
.Value = Replace(.Value, " ", " ")
End With

Ok, they have agreed to add a form to enter the data, so
that we have more options. It will be a very basic form
simply for data entry.

How would I do eliminate the double spaces with the
BeforeUpdate event? We prefer to use macros for this
specific problem, but if it can only be done with Visual
Basic, I can do that as well.

Thanks again,
ChelleV

-----Original Message-----
Hi ChelleV,

No, you can't do this in an Access table.

One solution would be to write a query that eliminates the double spaces
and have the user run this after they finish pasting in a batch of
records. Alternatively, use a form and adjust the text in its
BeforeUpdate event procedure.

Without background information that's about all one can say.

A user has an Access table that they paste information in
from another program (I am not familiar with any other
background info).

When they paste, often there is a double space showing up
in the text which is causing problems.

My question: can I write a macro or something that will
eliminate any and all double spaces upon entry into the
Access table, or at least once the row loses focus? They
are entering directly to the table, not using forms.

Thank you for your time and ideas.

ChelleV

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

The problem seems to have changed. In your original post you asked about
eliminating all double spaces; now it seems to be only double spaces
that follow a character other than a period. (What about other
punctuation marks?!)

To do the simple "replace two consecutive spaces with a single space",
in Access 2002 and later you can use something like
Replace([FieldName]," "," ")
in the Update To slot. (In most Access 2000 installations you can't call
the Replace() function in a query; instead you have to use a custom VBA
function which can itself call Replace(); in Access 97 there is no
Replace() and you have to write a custom function to do the job.

Replace() can't do context-sensitive replacement (e.g. only if the
precding character is not a period); doing this, whether in a query or
in an event procedure, will mean writing VBA code.

 

[ChelleV]

John,

Thank you for all of your addvice. This code below worked
in the BeforeUpdate event procedure. They have agreed to
simply change all double spaces, including following a
period, to single. In this case, it works fine.

Please ignore my later post, I wasn't sure anyone went
back in and read these nested messages.

Thanks again!

-----Original Message-----
Assuming you're using Access 2000 or later, use something like this in
the control's or form's BeforeUpdate event procedure. Must be VBA, can't
be a macro. Repeat for each control in which you want the replacement to
happen.

With Me.NameOfControl
.Value = Replace(.Value, " ", " ")
End With

Ok, they have agreed to add a form to enter the data, so
that we have more options. It will be a very basic form
simply for data entry.

How would I do eliminate the double spaces with the
BeforeUpdate event? We prefer to use macros for this
specific problem, but if it can only be done with Visual
Basic, I can do that as well.

Thanks again,
ChelleV

-----Original Message-----
Hi ChelleV,

No, you can't do this in an Access table.

One solution would be to write a query that eliminates the double spaces
and have the user run this after they finish pasting in

a
batch of

records. Alternatively, use a form and adjust the text

in
its

BeforeUpdate event procedure.

Without background information that's about all one can say.

On Mon, 15 Dec 2003 07:02:39 -0800, "ChelleV"

A user has an Access table that they paste information in
from another program (I am not familiar with any other
background info).

When they paste, often there is a double space showing up
in the text which is causing problems.

My question: can I write a macro or something that will
eliminate any and all double spaces upon entry into the
Access table, or at least once the row loses focus? They
are entering directly to the table, not using forms.

Thank you for your time and ideas.

ChelleV

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

I'm glad it worked.

When people answer a question they always look out for further posts in
the same thread. If you have a follow-up you should always post in the
original thread so that the whole context is available when people are
reading it (unless so many days have passed that the original messages
may no longer be available).

John,

Thank you for all of your addvice. This code below worked
in the BeforeUpdate event procedure. They have agreed to
simply change all double spaces, including following a
period, to single. In this case, it works fine.

Please ignore my later post, I wasn't sure anyone went
back in and read these nested messages.

Thanks again!

-----Original Message-----
Assuming you're using Access 2000 or later, use something like this in
the control's or form's BeforeUpdate event procedure. Must be VBA, can't
be a macro. Repeat for each control in which you want the replacement to
happen.

With Me.NameOfControl
.Value = Replace(.Value, " ", " ")
End With

Ok, they have agreed to add a form to enter the data, so
that we have more options. It will be a very basic form
simply for data entry.

How would I do eliminate the double spaces with the
BeforeUpdate event? We prefer to use macros for this
specific problem, but if it can only be done with Visual
Basic, I can do that as well.

Thanks again,
ChelleV

-----Original Message-----
Hi ChelleV,

No, you can't do this in an Access table.

One solution would be to write a query that eliminates
the double spaces
and have the user run this after they finish pasting in a
batch of
records. Alternatively, use a form and adjust the text in
its
BeforeUpdate event procedure.

Without background information that's about all one can
say.

On Mon, 15 Dec 2003 07:02:39 -0800, "ChelleV"

A user has an Access table that they paste information
in
from another program (I am not familiar with any other
background info).

When they paste, often there is a double space showing
up
in the text which is causing problems.

My question: can I write a macro or something that will
eliminate any and all double spaces upon entry into the
Access table, or at least once the row loses focus?
They
are entering directly to the table, not using forms.

Thank you for your time and ideas.

ChelleV

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.