ansaurus

Question

Removing non-alphanumeric characters in an Access Field.

Answer 1

A:

I'm not sure that the hyphens are actually the problem without seeing sample data / query but if all you need to do is get rid of them, the Replace function should be sufficient (you can use this in the query)

example: http://www.techonthenet.com/access/functions/string/replace.php

If you need to do some more advanced string manipulation than this (or multiple calls to replace) you might want to create a VBA function you can call from your query, like this:

http://www.pcreview.co.uk/forums/thread-2596934.php

To do this you'd just need to add a module to your access project, and add the function there to be able to use it in your query.

AlexCuse 2010-03-22 16:48:13

Unfortunately, the Find/Replace function only works for up to 65,000 entries. I've got at least 10x that amount :/

Jacques Tardie 2010-03-22 16:52:46

Where do you get that number? I haven't used access much lately but don't recall any such limitations regarding the number of rows a function would work on (perhaps someone more knowledgeable w/ access will be more helpful)Are you trying to do this while viewing the dataset, or using a query?

AlexCuse 2010-03-22 17:00:58

I tried doing it! Clipboard won't handle any more values than that.I'm doing some really simple work, just trying to append one field to another table, but I'm consistently getting validation errors on every entry.I haven't tried the vb function yet, to be honest mainly because it's not something I'm familiar with at all.

Jacques Tardie 2010-03-22 17:09:43

Btw, thanks for the help Alex.

Jacques Tardie 2010-03-22 17:10:30

Ah, I see. Looking at your followup comments, I think we'll need to see the query in question to get an idea of what it is you need to do (HansUp describes how you can get the query text to show us)

AlexCuse 2010-03-22 19:06:34

@Jacques Tardie: you obviously didn't read the link that @AlexCuse posted, which refers to the Replace() VBA function, which can be used in an UPDATE query from within Access. No one at any point was suggesting using the UI's Find/Replace function.

David-W-Fenton 2010-03-22 19:42:22

ansaurus

tags:

views:

answers:

Removing non-alphanumeric characters in an Access Field.

related questions