[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

CONCAT and SubStr in access

Posted on 2004-08-25
4
Medium Priority
?
9,875 Views
Last Modified: 2011-08-18
Hi..I urgently need the solution on the following case....please help

1. I would like to make a select statement using substr..but the access cannot process my query...it said the "substr" is not a valid command...

2. I have a field in the table to store the tel of client, some of the reocrd contact a "space" in-between the tel number...what is the fastest way to find out those record and correct them to "without space" in access??
0
Comment
Question by:esther_6694
[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
4 Comments
 
LVL 12

Accepted Solution

by:
fulscher earned 252 total points
ID: 11891080
esther 6694, concerning the 1st question: please post the query as you have it now.

Concerning the second question: first create a normal query:
SELECT (replace(tel, " ", "")) from tablename;
and check whether this gives the results which you need. If so, you can create an update query to fix the fields (make a backup first!!!):

UPDATE table SET tel = Replace([tel]," ","");

Hope this helps
Jan


0
 
LVL 9

Assisted Solution

by:solution46
solution46 earned 248 total points
ID: 11891081
The commands you need are Mid() for SubString and Replace() to get rid of the spaces.

The Access online help should tell you all you need to know about these functions.

s46.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

649 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