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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
One way:

Left(YourField,InStr(1,YourField,".")) & val(Mid(YourField,InStr(1,YourField,".")+1))

Left("B20.0008",InStr(1,"B20.0008",".")) & val(Mid("B20.0008",InStr(1,"B20.0008",".")+1))
returns B20.8
Gustav BrockCIOCommented:
Another way:

strNum = Split(YourField, ".")(0) & "." & Val(Split(YourField, ".")(1))

And yet another way...

strField = Left(YourField, InStr(1, YourField, ".")) & Replace(Right(YourField, InStr(1, YourField, ".") - 2), "0", "")


10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"the query code be?"

That implied this is in a query. So, Split() et al will not work ...

Hamed NasrRetired IT ProfessionalCommented:
'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
Gustav BrockCIOCommented:
> So, Split() .. will not work [in query]

You are right, but the Split code looks nicer!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
hnasr & p912s

What if the value is B20.000800999
You solution would replace ALL zeros
Dale FyeOwner, Developing Solutions LLCCommented:
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.
In the following example, you would replace X with your field name.

left(x,instr(x,".")) & clng(mid(x,instr(x,".")+1))

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What happens if the data is B20.000800ABC

Hamed NasrRetired IT ProfessionalCommented:
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?"
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, that's just two samples of data.
Dale FyeOwner, Developing Solutions LLCCommented:
Like I said, I would use a function.  The advantage is that you can add error handling and special processing to accommodate anomalies within your data.  I've included sample code here.  In a query, you could use something like:

Select [FieldName], ReformatField([FieldName]) as Reformatted
FROM yourTable

Hope this helps

Public Function ReformatField(SomeValue As Variant) As Variant

    Dim strLeft As String, strRight As String
    Dim intCharPos As String
    'Check to see whether the value passed was NULL
    'and if so, return NULL
    If IsNull(SomeValue) Then
        ReformatField = Null
        Exit Function
    End If
    'Check to see whether there is actually a period in the string
    'If not, return the original value
    intCharPos = InStr(SomeValue, ".")
    If intCharPos = 0 Then
        ReformatField = SomeValue
        Exit Function
    End If
    'You could also put some error checking in here to make sure
    'that the position of the period in the string is not the
    'first or last character.
    'Parse the string
    strLeft = left(SomeValue, intCharPos - 1)
    strRight = Mid(SomeValue, intCharPos + 1)
    'Remove leading zeros from the right side of the string
    'You could do this with the Replace function, but it would
    'replace all of the zeros, even if they were after the first
    'non-zero value
    While left(strRight, 1) = "0"
        strRight = Mid(strRight, 2)
    ReformatField = strLeft & "." & strRight
End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
topUKlawyerAuthor Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Gustav BrockCIOCommented:
Joe's original could handle Nulls this way:

Code: Left(YourField,InStr(1,Nz(YourField),".")) & Val(Mid(Nz(YourField),InStr(1,Nz(YourField),".")+1))

topUKlawyerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.