• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Trim Leading 0s from percentage input mask

Hello, I have a column in an Access table saved as a text field with a percentage input mask 999\%;;_.  Users have been entering data in this fashion 001 for 1%.  Can I trim the leading 0s from this field in any way?

Thank you,
Bevo
0
Bevos
Asked:
Bevos
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can use the Trim() function ... in an Update query ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Make a backup first.

Example:

UPDATE Table1 SET Table1.YourField = Trim([YourField]);


mx
0
 
dqmqCommented:
I don't think the trim function takes care of zeros.

Perhaps this:

iif(isnumeric([yourfield]),trim(cstr(cdec([Yourfield]))),[YourField])
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yeah ... I guess not.

Bevo ... if you change the data type to Numeric, the issue automatically goes away.  Can you do that?

mx
0
 
BevosAuthor Commented:
Yep, silly me numeric fixes everything quickly and easily
0
 
dqmqCommented:
I agree...change the datatype to numeric.  Even if you have to change it back to a string, that should clear the zeros.  The only issue is if you have non-numeric data
0
 
Nick67Commented:
Why work that hard?

UPDATE tblWhatever SET tblWhatever.PercentText = CInt([PercentText]);

The underlying field remains text.
CInt() or CLng() will whack leading zero.

My 2¢
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now