[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Right([String,?) Numbers only

Posted on 2011-09-25
15
Medium Priority
?
359 Views
Last Modified: 2013-11-28
Hi All
How do I get (from field "GlasType" using the Query grid) only the right hand side of these strings where I only want them if they are numbers i.e.
From "Pyro 7" I want  7
From "Pyro 15" I want  15
From "GWPP 16.2" I want  16.2
Can I extract the string part as numbers even though they are from a text field?
Thanks
0
Comment
Question by:DatabaseDek
[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
  • 8
  • 3
  • 3
  • +1
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36595149
"....where I only want them if they are numbers ..."

Does this mean that you have values that do NOT have numbers in the final positions?
0
 

Author Comment

by:DatabaseDek
ID: 36595191
Hi Peter

No, All stings end with numbers but I have strings where within say the last 4 characters there are letters as in Pyro 7
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36595284
This will do it if there is always a space preceding your number:

mid([YourFieldName],instrrev([YourFieldName]," "))
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 61

Expert Comment

by:mbizup
ID: 36595286
And if you need to explicity convert it to a number:

CDbl(mid([YourFieldName],instrrev([YourFieldName]," ")) )
0
 

Author Comment

by:DatabaseDek
ID: 36595315
Hi that's great

Unfortunately there often is no space between text and number. Is there any function that would simply say find first number? I have spent a couple of hours with it but can't see anything!
0
 

Author Comment

by:DatabaseDek
ID: 36595316
Hi

Isn't there a split function. (Is function the correct term?)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36595339
There is a split function which returns an array of values from your string that were seperated by some delimiter.   However if you just need a number from the right hand side, split is overkill and overly complicated.
0
 

Author Comment

by:DatabaseDek
ID: 36595401
Hi I've just looked up a Val Function it supposed to get the number value from a string but in the  query grid it doesn't. Any ideas?

Derek
0
 

Author Comment

by:DatabaseDek
ID: 36595406
Hi When I type  Right([GlassType],3) am I getting the last 3 numbers or the 3rd from the right?
0
 

Author Comment

by:DatabaseDek
ID: 36595410
Hi

If I could get just the 4th number from the right I could ask if fourth number from right is =>0 then
Do something, Do something else
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36595569
You can use this expression:

GlasTypeNo: StrReverse(Mid(Str(Val(StrReverse([GlasType] & "9"))),3))

/gustav
0
 

Author Comment

by:DatabaseDek
ID: 36595611
Hi
that looks promissing what does the "9" mean?
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 36595636
It is to prevent a trailing zero to be removed.

/gustav
0
 

Author Closing Comment

by:DatabaseDek
ID: 36595683
Tried it, works.

Brilliant.
Thank you
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36595763
You are welcome!

/gustav
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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