Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Take cell out of formula

I am using a formula that takes a number and writes it in letters according to what I have assigned each digit. I now want a button that will reverse it and put the letters back into the original number. Any idea the formula for another button to put it back in numbers? The third field name will be txtOutput2

The formula I am using to write in letters is as follows:

Dim arrayname
Dim i As Long
Dim bNumeric As Boolean
arrayname = Array("q", "w", "e", "r", "t", "y", "u", "i", "p", "a")

bNumeric = True
Me.txtOutput = Null
For i = 1 To Len(Me.txtInput)
    If IsNumeric(Mid(Me.txtInput, i, 1)) Then
    Else
        bNumeric = False
        i = Len(Me.txtInput)
    End If
Next
   

If bNumeric Then
    Me.txtOutput = ""
    For i = 1 To Len(Me.txtInput)
        Me.txtOutput = Me.txtOutput + arrayname(CInt(Mid(Me.txtInput, i, 1)))
    Next
Else
    MsgBox "Non-numeric symbols entered: " & Me.txtInput, vbOKOnly
End If

Me.fldDateUpdated = Now()

Open in new window

0
cansevin
Asked:
cansevin
  • 5
  • 3
1 Solution
 
als315Commented:
You can use this function:
Function conv_txt2num(in_str As String) As String
Dim arrayname
Dim i As Long, j As Long, out_str As String, c As String
Dim bNumeric As Boolean
arrayname = Array("q", "w", "e", "r", "t", "y", "u", "i", "p", "a")
out_str = ""
For i = 1 To Len(in_str)
    c = Mid(in_str, i, 1)
    For j = 0 To UBound(arrayname)
        If c = arrayname(j) Then
            out_str = out_str & j
            Exit For
        End If
    Next j
Next i
conv_txt2num = out_str
End Function

Open in new window

Call it like:
Me.txtOutput2 =  conv_txt2num(Me.txtInput)
0
 
cansevinAuthor Commented:
At the risk of sounding real stupid... is that something I can copy and past into an "OnClick" event? That is what I am looking for.
0
 
als315Commented:
You can paste function to module for use anywhere (in queries, on forms, etc). In "OnClick" event you can call function like in sample above:
Me.txtOutput2 =  conv_txt2num(Me.txtOutput)
ConvertNum.accdb
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
cansevinAuthor Commented:
Ok... what field names do I change?

My field names are: The letters I start with is txtOutput and the final numbers are txtOutput2.
0
 
cansevinAuthor Commented:
I figued out which to change... but when I click the button, nothing happens. It isn't putting the result in the field. The code I am using is:

Private Sub Command43_Click()

Dim arrayname
Dim i As Long, j As Long, out_str As String, c As String
Dim bNumeric As Boolean
arrayname = Array("q", "w", "e", "r", "t", "y", "u", "i", "p", "a")
out_str = ""
For i = 1 To Len(in_str)
    c = Mid(in_str, i, 1)
    For j = 0 To UBound(arrayname)
        If c = arrayname(j) Then
            out_str = out_str & j
            Exit For
        End If
    Next j
Next i
conv_txtOutput2 = out_str

End Sub

Open in new window

0
 
cansevinAuthor Commented:
I will upload the form I am working with. Any chance you can help? Thanks for everything.
form.accdb
0
 
als315Commented:
I prefer to use separate functions (look at Module1), because you can reuse in other cases. Copy Module1 to new DB and you can call this function from any form.
form.accdb
0
 
cansevinAuthor Commented:
It worked!!! Thanks... I am going to need help using it on a report.  I will post another question for that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now