[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Take cell out of formula

Posted on 2014-03-07
Medium Priority
320 Views
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()
0
Question by:cansevin
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

LVL 40

Expert Comment

ID: 39913126
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
Call it like:
Me.txtOutput2 =  conv_txt2num(Me.txtInput)
0

Author Comment

ID: 39913228
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 40

Expert Comment

ID: 39913251
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

ID: 39913275
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

Author Comment

ID: 39913405
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
0

Author Comment

ID: 39913421
I will upload the form I am working with. Any chance you can help? Thanks for everything.
form.accdb
0

LVL 40

Accepted Solution

als315 earned 2000 total points
ID: 39914272
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

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
###### Suggested Courses
Course of the Month13 days, 7 hours left to enroll