[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

statement to remove everything after the first space in access query

Posted on 2013-12-16
18
Medium Priority
?
477 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

825 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