asked on

statement to remove everything after the first space in access query

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])

Jim Dettman (EE MVE)

Simple enough:

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

Jim.

joylene6

ASKER

Thanks- I will give it a try right now.

joylene6

ASKER

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?

awking00

Replace
Left(InStr([PDM.Strength_Unit]," ")-1
with
Val(Left(InStr([PDM.Strength_Unit]," ")-1)

Jim Dettman (EE MVE)

Should be:

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

Jim.

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

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])

joylene6

ASKER

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])))

Rey Obrero (Capricorn1)

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.

awking00

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]))

joylene6

ASKER

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

awking00

Using only val on this example , 10 MG = 1 TAB, I believe would produce 101.

Rey Obrero (Capricorn1)

@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

.

Rey Obrero (Capricorn1)

@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

awking00

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)))

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joylene6

ASKER

awkingoo- trying now

joylene6

ASKER

awkingoo - that seemed to have worked. Testing some more... but I think that was it.