471,090 Members | 1,529 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

simple text manipulation

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
Mar 29 '06 #1
3 2401
<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
Mar 29 '06 #2
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

Mar 29 '06 #3

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
Mar 29 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by ross | last post: by
3 posts views Thread by Fabian | last post: by
1 post views Thread by Andrew Poulos | last post: by
6 posts views Thread by Armel Asselin | last post: by
1 post views Thread by blangela | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.