?
Solved

statement to remove everything after the first space in access query

Posted on 2013-12-16
18
Medium Priority
?
469 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 MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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

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.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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 …

762 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