Solved

Right([String,?) Numbers only

Posted on 2011-09-25
15
348 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 49

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 49

Accepted Solution

by:
Gustav Brock earned 500 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 49

Expert Comment

by:Gustav Brock
ID: 36595763
You are welcome!

/gustav
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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 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

16 Experts available now in Live!

Get 1:1 Help Now