 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / December 2003Deleting extra space at data entry time
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 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 [quoted text clipped - 11 lines] > >ChelleV -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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, [quoted text clipped - 29 lines] >Please respond in the newgroup and not by email. >. 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 [quoted text clipped - 48 lines] >>Please respond in the newgroup and not by email. >>. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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 [quoted text clipped - 64 lines] >Please respond in the newgroup and not by email. >. 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, > [quoted text clipped - 82 lines] >>Please respond in the newgroup and not by email. >>. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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 >-----Original Message----- >A user has an Access table that they paste information in [quoted text clipped - 13 lines] >ChelleV >. 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. On Mon, 15 Dec 2003 12:48:15 -0800, >One more question. I've been working with the Update >Query route. I can get the rows I want by adding this [quoted text clipped - 23 lines] >>ChelleV >>. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.