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

x
?
Solved

statement to remove everything after the first space in access query

Posted on 2013-12-16
18
Medium Priority
?
475 Views
Last Modified: 2013-12-16
Hello.   I have a field (strength) that I just need the numbers at the beginning of the field and I want to strip off everything else.  The numbers are not the same length.  

Also - it is part of an Iff,then statement.  The IIf then statement is working, I need to find a wat just to get the first numbers of the field - and put it in my iif statement. The Strength field is the one that I need that data stripped from.

Here is an example of the data
Strength:
1000 MG = 26.32 ML
200 MG = 5.26 ML
10 MG = 1 TAB
1 CAP = 1 CAP

I want it to look like this:
1000
200
10
1



Dose: IIf([PDM].[Strength] Is Null Or [PDM].[Strength_Unit] Is Null,[PDM].[Volume_Number],[PDM].[Strength])
0
Comment
Question by:joylene6
[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
  • 6
  • 5
  • 4
  • +1
18 Comments
 
LVL 58
ID: 39721646
Simple enough:

Dose:IIF(instr(Nz([Strength_Unit],"")," ")>1, Left(instr([Strength_Unit]," ")-1),[Strength_Unit])

Jim.
0
 
LVL 1

Author Comment

by:joylene6
ID: 39721702
Thanks- I will give it a try right now.
0
 
LVL 1

Author Comment

by:joylene6
ID: 39721754
I changed it to
 Dose1: IIf(InStr(Nz([PDM.Strength_Unit],"")," ")>1,Left(InStr([PDM.Strength_Unit]," ")-1,[PDM.Strength_Unit]))

But I am getting a data mismatch. The fields are text and I cannot change.  Does there need to have a VAL or something stuck in there to convert?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 32

Expert Comment

by:awking00
ID: 39721794
Replace
Left(InStr([PDM.Strength_Unit]," ")-1
with
Val(Left(InStr([PDM.Strength_Unit]," ")-1)
0
 
LVL 58
ID: 39721878
Should be:

 Dose1: IIf(InStr(Nz([PDM.Strength_Unit],"")," ")>1,Left(InStr([PDM.Strength_Unit]," ")-1),[PDM.Strength_Unit])

Jim.
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39721896
Whoops, missed an argument:

Dose1: IIf(InStr(Nz([PDM.Strength_Unit],"")," ")>1,Left([PDM.Strength_Unit],InStr([PDM.Strength_Unit]," ")-1),[PDM.Strength_Unit])

Sorry,
Jim.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39721907
try this


try this


Dose1: IIf(InStr(Nz([PDM.Strength_Unit],"")," ")>1,Left([PDM.Strength_Unit], InStr([PDM.Strength_Unit]," ")-1),[PDM.Strength_Unit])

or this

Dose1: IIf(InStr(Nz([PDM].[Strength_Unit],"")," ")>1,Left([PDM].[Strength_Unit], InStr([PDM].[Strength_Unit]," ")-1),[PDM].[Strength_Unit])
0
 
LVL 1

Author Comment

by:joylene6
ID: 39721961
the Val(Left(InStr([PDM.Strength_Unit]," ")-1)  seems to have worked... but how do I add the VAL into the rest of the statement?

Dose1: IIf(InStr(Nz([PDM.Strength_Unit],"")," ")>1,Val(Left(InStr([PDM.Strength_Unit]," ")-1,[PDM.Strength_Unit])))
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39721978
if you want to use VAL(), you don't need the IIF() statement

Dose:Val([PDM.Strength_Unit])  

 is enough.. this will give the consecutive numerical values of the the field starting from position 1 and will stop when it finds a non numerical value


the last post of Jim should work.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39722030
Didn't read the original question properly and only addressed the conversion to number issue.
Assuming you want to return the volume_number if either strength or strength_unit is null and the numeric portion of the strength otherwise -
IIf([PDM].[Strength] Is Null Or [PDM].[Strength_Unit] Is Null,[PDM].[Volume_Number],Val(Left(InStr([PDM.Strength]," ")-1)))
The above assumes the strength will always begin with a number followed by a space. If it can exist as a stand alone number, add the test Iif(instr(strength,' ') > 0,Val(Left(InStr([PDM.Strength]," ")-1),[PDM.Strength]))
0
 
LVL 1

Author Comment

by:joylene6
ID: 39722061
welll... I need an if then becasue if Strength_unit or Strength is blank then I need Volume number to populate.

This is what I used,  but it brought back all zeros instead of the number

Dose1: IIf(InStr(Nz([PDM.Strength],"")," ")>1,Val(Left(InStr([PDM.Strength_Unit]," ")-1,[PDM.Volume_Number])))

original field       What query brought back    what I expect
30 MG = 0.3 ML      0                                        30
30 MG = 0.3 ML      0                                        30
1 TAB = 1 TAB      0                                         1
1 TAB = 1 TAB      0                                         1
1 TAB = 1 TAB      0
0
 
LVL 32

Expert Comment

by:awking00
ID: 39722070
Using only val on this example , 10 MG = 1 TAB, I believe would produce 101.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39722081
@joylene6,

if you read post at http:#a39721907  and http:#a39721896

you should not be using this

Dose1: IIf(InStr(Nz([PDM.Strength],"")," ")>1,Val(Left(InStr([PDM.Strength_Unit]," ")-1,[PDM.Volume_Number])))

which is WRONG.

please go back to posted comment and change your formula


.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39722088
@awking00

<Using only val on this example , 10 MG = 1 TAB, I believe would produce 101. >
that is not correct

val("10 MG= 1 TAB")    will give you  10
0
 
LVL 32

Expert Comment

by:awking00
ID: 39722092
And what did this produce?
IIf([PDM].[Strength] Is Null Or [PDM].[Strength_Unit] Is Null,[PDM].[Volume_Number],Val(Left(InStr([PDM.Strength]," ")-1)))
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 39722128
I stand corrected. So the statement just needs to be -
IIf([PDM].[Strength] Is Null Or [PDM].[Strength_Unit] Is Null,[PDM].[Volume_Number],Val[PDM.Strength])
0
 
LVL 1

Author Comment

by:joylene6
ID: 39722141
awkingoo- trying now
0
 
LVL 1

Author Comment

by:joylene6
ID: 39722174
awkingoo - that seemed to have worked.  Testing some more... but I think that was it.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 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