Solved

statement to remove everything after the first space in access query

Posted on 2013-12-16
18
463 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 57
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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 57
ID: 39721878
Should be:

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

Jim.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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 250 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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