?
Solved

Right([String,?) Numbers only

Posted on 2011-09-25
15
Medium Priority
?
355 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 51

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 51

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 51

Expert Comment

by:Gustav Brock
ID: 36595763
You are welcome!

/gustav
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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