Richard
asked on
access string: replace zeros
i have a field with a number of entries like
S202.005 and B20.0008
can i parse these to select and delete any zeros after the dota so they become
s202.5 B20.8
what would the query code be?
S202.005 and B20.0008
can i parse these to select and delete any zeros after the dota so they become
s202.5 B20.8
what would the query code be?
Another way:
strNum = Split(YourField, ".")(0) & "." & Val(Split(YourField, ".")(1))
/gustav
strNum = Split(YourField, ".")(0) & "." & Val(Split(YourField, ".")(1))
/gustav
And yet another way...
strField = Left(YourField, InStr(1, YourField, ".")) & Replace(Right(YourField, InStr(1, YourField, ".") - 2), "0", "")
HTH
Scot
strField = Left(YourField, InStr(1, YourField, ".")) & Replace(Right(YourField, InStr(1, YourField, ".") - 2), "0", "")
HTH
Scot
"the query code be?"
That implied this is in a query. So, Split() et al will not work ...
mx
That implied this is in a query. So, Split() et al will not work ...
mx
'assume your field name: ss
'assume . exists in all fields, otherwise you need to check for that.
SELECT ss, left(ss,instr(ss,".")-1) & replace(ss,"0","",instr(ss ,".")) AS myField FROM myTable
'assume . exists in all fields, otherwise you need to check for that.
SELECT ss, left(ss,instr(ss,".")-1) & replace(ss,"0","",instr(ss
> So, Split() .. will not work [in query]
You are right, but the Split code looks nicer!
/gustav
You are right, but the Split code looks nicer!
/gustav
hnasr & p912s
What if the value is B20.000800999
You solution would replace ALL zeros
What if the value is B20.000800999
You solution would replace ALL zeros
Personally, I would create a function to do this. Complex queries like this lend themselves to functions which can be documented and include error handling.
If you put it in a function, you could use any of the ideas mentioned above.
If you put it in a function, you could use any of the ideas mentioned above.
In the following example, you would replace X with your field name.
left(x,instr(x,".")) & clng(mid(x,instr(x,".")+1))
What happens if the data is B20.000800ABC
?
?
This, I think, is supposed to be a simple question, not a project to invite for further assumptions on the author's behalf.
"S202.005 and B20.0008
......
s202.5 B20.8
what would the query code be?"
"S202.005 and B20.0008
......
s202.5 B20.8
what would the query code be?"
Well, that's just two samples of data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow some big hitting stuff here:many thanks all you guys...
I appreciate the learned discussion.
Fyed: why do you use Public function rather than Function.
I appreciate the learned discussion.
Fyed: why do you use Public function rather than Function.
Just the way I was taught, 20 years ago. Have always done it that way.
In code modules they are all defined as Public, in Form and Report modules, I generally make them private, unless I want to be able to call that function from some outside the form/report.
In code modules they are all defined as Public, in Form and Report modules, I generally make them private, unless I want to be able to call that function from some outside the form/report.
Joe's original could handle Nulls this way:
Code: Left(YourField,InStr(1,Nz( YourField) ,".")) & Val(Mid(Nz(YourField),InSt r(1,Nz(You rField),". ")+1))
/gustav
Code: Left(YourField,InStr(1,Nz(
/gustav
ASKER
Thanks everyone.
I havent got time to test them all but my eye was drawn to fyeds function solution which i tried and worked very well.
Also it introduced me to "while....wend" which I hadnt come across before and for those not particularly logical reasons I am awarding the points to fyed. Thanks for all replying and I may well be able to test and use your solutions in the future.
I havent got time to test them all but my eye was drawn to fyeds function solution which i tried and worked very well.
Also it introduced me to "while....wend" which I hadnt come across before and for those not particularly logical reasons I am awarding the points to fyed. Thanks for all replying and I may well be able to test and use your solutions in the future.
Left(YourField,InStr(1,You
Example:
Left("B20.0008",InStr(1,"B
returns B20.8