Solved

statement to remove everything after the first space in access query

Posted on 2013-12-16
18
455 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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Simple enough:

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

Jim.
0
 
LVL 1

Author Comment

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

Author Comment

by:joylene6
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Replace
Left(InStr([PDM.Strength_Unit]," ")-1
with
Val(Left(InStr([PDM.Strength_Unit]," ")-1)
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
Using only val on this example , 10 MG = 1 TAB, I believe would produce 101.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
@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 119

Expert Comment

by:Rey Obrero
Comment Utility
@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 31

Expert Comment

by:awking00
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 250 total points
Comment Utility
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
Comment Utility
awkingoo- trying now
0
 
LVL 1

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now