Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find & Replace (Access 97 SR-2)

    I have a large table of data that I need to clean up a fair bit - it contains many 'double spaces' between words instead of just one space... I don't know why it's like this but I blame the data entry people =)

    Anyway, in this situation I usually use the find and replace window to search the whole table for " " (2 spaces) and replace with " " (1 space) which works fine but I'm trying to develop a VBA routine which would automatically do this process when called from a macro or similar.

    Can anyone please give me some code examples that might be able to do something along those lines? I've had a few goes myself but can't seem to get anything to work - I'm still new to VBA but learning a lot every day!

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find & Replace (Access 97 SR-2)

    The Edit/Replace... menu option in Access 97 is interactive; there is no DoCmd equivalent, unlike Edit/Find..., for which DoCmd.FindRecord exists. There *is* RunCommand acCmdReplace, but that only opens the interactive Replace dialog.
    You might control this dialog by sending the required keystrokes with the SendKeys instruction, but maintenance of the code would be a problem, and SendKeys is reputed to be flaky.
    Another option would be to create an update query that changes a field to its value with double spaces converted to single spaces. You would need to use a custom replace function, for Access 97 VBA has no built-in Replace. You'll find such a function in <post#=168756>post 168756</post#>. In the Change To box, you would enter<pre>ReplaceString([fieldname]," "," ")</pre>

    where fieldname should be replaced by the actual field name.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find & Replace (Access 97 SR-2)

    Thanks Hans, that's an excellent piece of code and does the trick! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •