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]
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
Last Comment
joylene6
ASKER
Thanks- I will give it a try right now.
ASKER
I changed it to
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?
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_U
with
Val(Left(InStr([PDM.Streng
Left(InStr([PDM.Strength_U
with
Val(Left(InStr([PDM.Streng
Should be:
Dose1: IIf(InStr(Nz([PDM.Strength
Jim.
Dose1: IIf(InStr(Nz([PDM.Strength
Jim.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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
or this
Dose1: IIf(InStr(Nz([PDM].[Streng
try this
Dose1: IIf(InStr(Nz([PDM.Strength
or this
Dose1: IIf(InStr(Nz([PDM].[Streng
ASKER
the Val(Left(InStr([PDM.Streng
Dose1: IIf(InStr(Nz([PDM.Strength
Dose1: IIf(InStr(Nz([PDM.Strength
if you want to use VAL(), you don't need the IIF() statement
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.
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]
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
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
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
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
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]
IIf([PDM].[Strength] Is Null Or [PDM].[Strength_Unit] Is Null,[PDM].[Volume_Number]
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
awkingoo- trying now
ASKER
awkingoo - that seemed to have worked. Testing some more... but I think that was it.
Dose:IIF(instr(Nz([Strengt
Jim.