Simple Update Query Question

[Rebecca]

Greetings.

I have a column ("G") full of words. I want to change the
letters in these words to numbers. Using an example, say
I want to change the letter "a" to "01" in all the words
in this columnn ("b" would be "02," and so on). I tried
in the criteria using only "a," but of course this only
updates those rows with only one letter, namely "a." How
do I "update" (actually change) the individual letters in
all the words? Thanks.

 

[Rick Brandt]

Greetings.

I have a column ("G") full of words. I want to change the
letters in these words to numbers. Using an example, say
I want to change the letter "a" to "01" in all the words
in this columnn ("b" would be "02," and so on). I tried
in the criteria using only "a," but of course this only
updates those rows with only one letter, namely "a." How
do I "update" (actually change) the individual letters in
all the words? Thanks.

You need a Replace() function. Access 2K and above have this built in. For Access
97 you would have to build your own (you might find examples with a Google search).
Oddly enough, while Access 2K has a Replace function, I don't think it works in
queries. Haven't heard if this is still true in Access 2002. At any rate, with
those versions you just need to write a custom VBA function that does nothing more
than use the built in Replace function to do the work. Your custom function _can_ be
used in your update query.

 

[Rebecca]

Thanks for your help, but your answer is only
comprehensible to someone who already knows how to do this
(this is a common pedagogical weakness among people who
try to teach ACCESS to beginners -- they mistakenly assume
the learner knows what the teacher knows). Could you
please be a little more specific, and explain step by step
how to do this in ACCESS 2002? Thanks.

-----Original Message-----


You need a Replace() function. Access 2K and above have this built in. For Access
97 you would have to build your own (you might find

examples with a Google search).

Oddly enough, while Access 2K has a Replace function, I don't think it works in
queries. Haven't heard if this is still true in Access 2002. At any rate, with
those versions you just need to write a custom VBA

function that does nothing more

 

[John Spencer (MVP)]

This might require you writing a function that would step through all the
characters and replace them with two digits. Telling you how to do this would
require you to tell us exactly what you wish to do. For instance, should
uppercase A get the same value as lowercase a? What is the substitution value
for each character? Do the substitution values follow a pattern of some type -
A = 01, B = 02, C = 03, etc.

As far as assuming the level of knowledge of the student - yes that is difficult
since I don't have any knowledge of your background. Are you a programmer, are
you a novice, are you temporarily stuck due to a brain cramp?

A sample vba function follows that will turn a string into a series of three
digits based on the ASCII equivalent of the letters. WARNING AIR CODE FOLLOWS
(and I have had two glasses of wine)

Public Function fEncodeString(strIN) as String
Dim intString as integer, intLoop as integer
Dim strOut as String

For intLoop = 1 to Len(strIN)
strOut = strOut & Format(Asc(Mid(StrIn,intLoop,1))<"000")
End For

fEncodestring = strOut
End Function

I am guessing at the level of expertise of the user.
Do you know how to create a user defined function in a module?
Do you know how to call that function in a query or on a form or in a report?

I hope this helps, but ...