Solved

Take cell out of formula

Posted on 2014-03-07
8
313 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
[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
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 40

Expert Comment

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

Open in new window

Call it like:
Me.txtOutput2 =  conv_txt2num(Me.txtInput)
0
 

Author Comment

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

by:als315
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

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

Open in new window

0
 

Author Comment

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

by:
als315 earned 500 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

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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