Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Report Translate letters to numbers

Posted on 2014-03-10
13
Medium Priority
?
255 Views
Last Modified: 2014-03-10
I have a cell of a table that has numbers stored as letters. On the report, I need the result to be the letters translated back to numbers. The table cell with the letters is set1. The letters numeric value is:

q = 0, w = 1, e = 2, r = 3, t = 4,  y = 5, u = 6, i = 7, p = 8, a = 9

Any idea how I would do this on a report?

Thanks!

Chris
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
  • 7
  • 6
13 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39918475
Not clear what exactly your table structure and data is like.

Does your table have two fields - one for the letter and another for its corresponding number?

If so, you can get a letter's number by setting the control source of a text box as follows:

= DLookup("YourNumberField", "YourTableName", "YourLetterField = '" & YourLetter & "'")

Open in new window

0
 

Author Comment

by:cansevin
ID: 39918481
The table only has the info with the letters. The numbers is not saved on a table. That is why it will somehow need to translate it back to numbers. Any ideas?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39918529
Then how do you know (generically) which letters correspond to which numbers...?

Or are we talking about ONLY those letters, with exact matches to ONLY those numbers?

If you have any control over the table design as a developer, I would suggest *adding* a field to that table so that you can map each letter to a unique number.  By doing that, you can easily 'look up' any number from a letter (or vice versa) using the DLookup syntax in my earlier comment.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:cansevin
ID: 39918544
Sorry for the confusion there... those are the only letters that will ever be in the value. So it is a straight translation. There will never be any other letters to translate. Totally understand it would be easier if there was more info in the table. Unfortunately, I can't control that.

Thanks!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39918549
<<  I would suggest *adding* a field to that table so that you can map each letter to a unique number.>>

Actually, re-reading your question... to be clear, what I am suggesting is to create a separate Lookup Table if you don't have one already, called tblLookupNumbers that simply looks like this:

Letter    Num
   Q             0
    W           1
    E              2
    R              3
     T             4
     Y             5
     U            6
      I             7

Then the textbox control source would be this:

= DLookup("Num", "tblLookupNumber", "Letter= '" & YourLetter & "'")

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39918555
<<
Totally understand it would be easier if there was more info in the table. Unfortunately, I can't control that.
>>

A separate lookup table should be possible, even if you don't have control over the table you have been describing.  The Lookup Table can be part of the Front-End interface that contains your report.
0
 

Author Comment

by:cansevin
ID: 39918557
Ok... would that option work for a string of letters. For example:

wwrrtt would show up "113344"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39918572
It would work for mapping letters to numbers.  But for a string of letters (with or without a lookup table), you would need code to parse the string letter by letter, replacing the letters with their corresponding numbers.

I don't have the time to help with that at the moment, but perhaps someone else does - and I'll check back in later this evening otherwise.
0
 

Author Comment

by:cansevin
ID: 39918590
Totally understand... thanks!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39918997
You can use a series of nested Replace commands, like this:


Replace(Replace(Replace(Replace(YourString, "Q","0"), "W","1"),"E","2"),"R","3")  ... etc

Open in new window

Or in a more readable format:


s = Replace(YourString, "Q","0")
s = Replace(s, "W","1")
s = Replace(s, "E","2")
s = Replace(s, "R","3")
s = Replace(s, "T","4")
s = Replace(s, "Y","5")

' etc

Open in new window



That can either be done harcoded as above, or using a lookup table (described earlier) which is more flexible if you might ever need to add to or change these mappings:


s = Replace(YourString, "Q", DLookup("Num", "tblLookupNumber", "Letter= 'Q'"))
s = Replace(s, "W", DLookup("Num", "tblLookupNumber", "Letter= 'W'"))
s = Replace(s, "E",  DLookup("Num", "tblLookupNumber", "Letter= 'E'"))
s = Replace(s, "R",  DLookup("Num", "tblLookupNumber", "Letter= 'R'"))  
' Etcetera

Open in new window

0
 

Author Closing Comment

by:cansevin
ID: 39919131
Thanks!
0
 

Author Comment

by:cansevin
ID: 39919164
mbizup... I am so sorry. I used your long "replace" and it worked great. I awarded the points to your wrong post. Hope that it is still ok. Thanks for the help!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39919221
No worries... Glad you got it working.    :)
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

610 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