Solved

Take cell out of formula

Posted on 2014-03-07
8
307 Views
Last Modified: 2014-03-10
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
Comment
Question by:cansevin
  • 5
  • 3
8 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
 

Author Comment

by:cansevin
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
 

Author Comment

by:cansevin
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:cansevin
Comment Utility
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
 

Author Comment

by:cansevin
Comment Utility
I will upload the form I am working with. Any chance you can help? Thanks for everything.
form.accdb
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
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
 

Author Comment

by:cansevin
Comment Utility
It worked!!! Thanks... I am going to need help using it on a report.  I will post another question for that.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now