Query to clean up ugly data

[ranch]

Greetings all,

I have imported a member table that I need to clean up. I have many fields that have misc characters that have some how slipped in that are not alphanumeric, such as quote signs and commas. I have used several examples on this borad to break up fields that were combined into one field such as firstname & lastname via the update Query. This has worked liked a charm, but now I am stuck. I am new to access so excuse the "stupid" questions, but I can't find some real basic answers.

There is alot of sample code that is very helpful but I am often at a loss where to put or define it. Some appers to be VBA code, which I know little about, but can't a function just be copied & pasted for a one time use. If that is possible, where to copy it too so it can be called in a query.?

Example: I searched & found this post:

http://www.access-programmers.co.uk/forums/showthread.php?threadid=12510&highlight=remove+characters

that seems like it would be very helpfull, but I have no idea where to put it. I assume I call it from an update query under the "Update To:" line(?). But where is the function defined. How does it get called e.g. functionname(fieldname) ? What line do I put it on?

If someone could write it in an EXAMPLE that would be most helpful.

I am sure I am missing something obvious, but if anyone could lend a hand I would appeciate it.

Thanks again.
Mike

 

[Jon K]

Suppose you have a table tblFriends with a Name field which contains:
'John', White
'Mary', H. W. Pilcher

and you want to remove all the quotes, commas and dots.

In Fornatian's post, select from Public Function RejectSpacesAndDashes..... up to End Function. Click Copy.

In Access, click New in the Modules tab. Move the cursor to a blank line and click Paste. Now you have copied and pasted Fornatian's function to a module.

Change the word "RejectSpacesAndDashes" to "RemoveQuoteCommaDot" to give a new name to the function. (There are two instances of this word. Both need to be changed.)

Find the line that reads:
If Mid(TheWord, i, 1) = " " Or Mid(TheWord, i, 1) = "-" Then

and change it to:
If Mid(TheWord, i, 1) = "'" Or Mid(TheWord, i, 1) = "," Or Mid(TheWord, i, 1) = "." Then

Close the module. Save it as MyModule. You have created a public function called RemoveQuoteCommaDot.

You can now use it in a make-table query to remove the quotes, commas and dots from the names in tblFriends.

Open a new query. Switch to SQL View. Type this query:

SELECT RemoveQuoteCommaDot(Name) AS Friends
INTO tblNewTable
FROM tblFriends;

Run this query to create the new table.


A public function in a Module can be used any where (queries, forms, reports) in the database as if it were one of Access's built-in functions.

Hope this helps.

 

[ranch]

Jon,

Worked perfectly!....I made several more functions via the method you outlined.

It is easy to get lost in all the terminolgy......Thanks for clearing it up.

Mike