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

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]

Microsoft AccessMicrosoft ApplicationsSQL

Thanks- I will give it a try right now.

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?

Dose1: IIf(InStr(Nz([PDM.Strength

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?

Replace

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

with

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

Left(InStr([PDM.Strength_U

with

Val(Left(InStr([PDM.Streng

Should be:

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

Jim.

Dose1: IIf(InStr(Nz([PDM.Strength

Jim.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

try this

Dose1: IIf(InStr(Nz([PDM.Strength

or this

Dose1: IIf(InStr(Nz([PDM].[Streng

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

Dose1: IIf(InStr(Nz([PDM.Strength

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.

Dose:Val([PDM.Strength_Uni

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.

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

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]

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.Stre

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

This is what I used, but it brought back all zeros instead of the number

Dose1: IIf(InStr(Nz([PDM.Strength

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

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

@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

.

if you read post at http:#a39721907 and http:#a39721896

you should not be using this

Dose1: IIf(InStr(Nz([PDM.Strength

which is WRONG.

please go back to posted comment and change your formula

.

@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

<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

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

IIf([PDM].[Strength] Is Null Or [PDM].[Strength_Unit] Is Null,[PDM].[Volume_Number]

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

awkingoo- trying now

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

Dose:IIF(instr(Nz([Strengt

Jim.