Solved

access string: replace zeros

Posted on 2012-03-23
17
367 Views
Last Modified: 2012-03-29
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?
0
Comment
Question by:topUKlawyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +4
17 Comments
 
LVL 75
ID: 37760111
One way:


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

Example:
Left("B20.0008",InStr(1,"B20.0008",".")) & val(Mid("B20.0008",InStr(1,"B20.0008",".")+1))
returns B20.8
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37760402
Another way:

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

/gustav
0
 
LVL 18

Expert Comment

by:p912s
ID: 37760896
And yet another way...

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

HTH

Scot
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 75
ID: 37761043
"the query code be?"

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

mx
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37761411
'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
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37761603
> So, Split() .. will not work [in query]

You are right, but the Split code looks nicer!

/gustav
0
 
LVL 75
ID: 37761903
hnasr & p912s

What if the value is B20.000800999
You solution would replace ALL zeros
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37762836
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.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37763555
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

0
 
LVL 75
ID: 37763625
What happens if the data is B20.000800ABC

?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 37763652
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?"
0
 
LVL 75
ID: 37763662
Well, that's just two samples of data.
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 500 total points
ID: 37765499
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
Dale


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)
    Wend
   
    ReformatField = strLeft & "." & strRight
       
End Function
0
 

Author Comment

by:topUKlawyer
ID: 37765555
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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37765576
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.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 37765663
Joe's original could handle Nulls this way:

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

/gustav
0
 

Author Comment

by:topUKlawyer
ID: 37784563
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.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question