simple text manipulation

[ChasW]

Is it possible, within Access, to create an update query that replaces
all spaces with underscores for text data in a given field?

I am aware that it can be done by going into Edit -> Replace, but an
update query would be much preferred.

Thank you in advance,
Charles

[Rick Brandt]

<ChasW> wrote in message news:mp********************************@4ax.com...

Is it possible, within Access, to create an update query that replaces
all spaces with underscores for text data in a given field?

I am aware that it can be done by going into Edit -> Replace, but an
update query would be much preferred.

UPDATE TableName
Set FieldName = Replace(FieldName, " ", "_")

Access 2000 (if patched) or higher.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[ChasW]

On Wed, 29 Mar 2006 01:10:14 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

<ChasW> wrote in message news:mp********************************@4ax.com...

Is it possible, within Access, to create an update query that replaces
all spaces with underscores for text data in a given field?

I am aware that it can be done by going into Edit -> Replace, but an
update query would be much preferred.

UPDATE TableName
Set FieldName = Replace(FieldName, " ", "_")

Access 2000 (if patched) or higher.

Thanks for making me aware of Replace(), but despite my having access
2000 patched to sp3 and msjet 4.0 patched to the latest version, I am
getting "Undefined function 'Replace' in expression"

... so I did some reading and some say that patching fixes this and
others say that you need to wrap a vb function since msjet does not
support Replace..

I am not sure what the deal is with this function other than it
remains undefined after my patching.

So, since I believe I am correctly patched, I tried making my first VB
function ( reasonably fluent with C++, but a complete VB noob )

Function ReplaceText(StringIn as String) as String
Dim NewString as String
NewString = Replace(StringIn," ","_")
ReplaceText = NewString
End function

basically, what I got from here:
http://www.mcse.ms/message1580720.html

but I am not sure how to make this work in my query

Here are two versions of my query, neither yet to work:

UPDATE collection_tbl
SET collection_tbl.[alt name] = Replace(collection_tbl.[name], " ",
"_");

and this one that does not seem to be seeing my new module:

UPDATE collection_tbl
SET collection_tbl.[alt name] = ReplaceText(collection_tbl.[name]);

Any additional help would be appreciated.

Best regards,
Charles

[ChasW]

and this one that does not seem to be seeing my new module:

UPDATE collection_tbl
SET collection_tbl.[alt name] = ReplaceText(collection_tbl.[name]);

not sure why, but i deleted the module from access and re created it
and it saw the newly added wrapper function this time.

thanks for the help!
Charles