?
Solved

Access 2010 Replace value

Posted on 2013-05-23
9
Medium Priority
?
370 Views
Last Modified: 2013-05-23
I need to remove the 1st zero after the fist dash in an access query.
Example:
75-03-0140 Before
75-3-0140 After

Any thoughts -- Thanks
0
Comment
Question by:shieldsco
[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
9 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39190951
use this expression in your query

Replace([FieldName],"-0","-",1) AS Expr1

OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 39190955
Missed a comma

Replace([FieldName],"-0","-",,1) AS Expr1

OM Gang
0
 
LVL 21
ID: 39190978
No points needed.

OM Gang, You left out a comma before the 1.

Should be:

Replace([FieldName],"-0","-", ,1)

or

Replace([FieldName],"-0","-",1 ,1)

Otherwise it gets gets leading zeros.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Closing Comment

by:shieldsco
ID: 39191195
Thanks
0
 

Author Comment

by:shieldsco
ID: 39191257
I was a little hasty with the code Replace([FieldName],"-0","-",1 ,1)
 does not work in all cases. For Example:

75-01-0140 Before
75-1-0140 After - Ok

75-10-0140 Before
75-10-140 Incorrect -- Only if  zero after the first dash other wise do nothing
75-10-0140 After - Ok
0
 
LVL 28

Expert Comment

by:omgang
ID: 39191280
Not sure we'll be able to do that in a query expression.  Perhaps.....I'll give it a go.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39191384
Try this

IIf(InStr([FieldName],"-0")<5,Replace([FieldName],"-0","-",1,1),[FieldName])

Basically, it checks to see if "-0" occurs in the first five characters of the string and only performs the Replace function if so.  It will work if your data is consistent that the "-0" will always be within the first five characters.

OM Gang
0
 

Author Comment

by:shieldsco
ID: 39191439
Works Good Thanks
0
 
LVL 28

Expert Comment

by:omgang
ID: 39191450
You're welcome.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

752 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