number of occurances of letter in a field then multiplied by value

[adam]

I am trying to write a code where A=1, B=2, C=3 then add each letter value to get a total

for instance "cat" would be 3+1+20=2

I am trying to find way to return the number of occurances of a letter in a field then multiply by the the set value. I was planning on doing this for each letter in a query then adding them in another query which I would use in a form. the result would be: I type in a word and it would return the total value

 

[John Vinson]

I am trying to write a code where A=1, B=2, C=3 then add each letter value to get a total.

for instance "cat" would be 3+1+20=24

I am trying to find way to return the number of occurances of a letter in a field then multiply by the the set value. I was planning on doing this for each letter in a query then adding them in another query which I would use in a form. the result would be: I type in a word and it would return the total value

This would be impossible in a Table and rather difficult in a Query
(and you're asking in the tablesdbdesign newsgroup). I'd suggest a
simple VBA function to Kabbalize words in this manner. UNTESTED air
code:

Public Function WordVal(strW As String) As Long
Dim iPos As Integer
strW = LCase(strW) ' avoid case conversion problems
WordVal = 0
For iPos = 1 to Len(strW)
WordVal = WordVal + Asc(Mid(strW, iPos, 1))) - 96 ' 97 = "a"
Next iPos
End Function

 

[Tim Ferguson]

I am trying to write a code where A=1, B=2, C=3 then add each letter
value to get a total.

for instance "cat" would be 3+1+20=24

Public Function AddUpTheLetters(TheString As String) As Long
' assume that TheString is not null, and is all LCase
'
Dim w as Integer
Dim dwZeroChar as Long

dwZeroChar = Asc("a") -1 ' Shorthand

For w = 1 To Len(TheString)
dwTotal = dwTotal + Asc(Mid$(TheString),w,1) - dwZeroChar
Next w

AddUpTheLetters = dwTotal

End Function

I am trying to find way to return the number of occurances of a letter
in a field then multiply by the the set value.

I don't really understand this, but is it the same as what you asked for
above?

which I would use in a form. the result would be: I type in a word and
it would return the total value

Can't you just do it on a form: call the function from the AfterUpdate
event? You can call a VBA function from a query, but only via the GUI; for
example using DoCmd.OpenQuery or in the recordset behind a form.

Hope that helps


Tim F

 

[Guest]

Create a string of 26 characters (or 52 for counting >9).
Start with 26 zeros. Then increment the character
corresponding to the letter using the Mid function.

-----Original Message-----
I am trying to write a code where A=1, B=2, C=3 then add

each letter value to get a total.

for instance "cat" would be 3+1+20=24

I am trying to find way to return the number of

occurances of a letter in a field then multiply by the the
set value. I was planning on doing this for each letter in
a query then adding them in another query which I would
use in a form. the result would be: I type in a word and
it would return the total value