Solved

Right([String,?) Numbers only

Posted on 2011-09-25
15
349 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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.

932 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

13 Experts available now in Live!

Get 1:1 Help Now