468,766 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,766 developers. It's quick & easy.

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 2364
<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 this site, you agree to our Privacy Policy and Terms of Use.