Solved

Right([String,?) Numbers only

Posted on 2011-09-25
15
352 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.

808 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