Solved

How do I concatenate pieces of text from one field to format them differently for another field?

Posted on 2008-09-29
4
219 Views
Last Modified: 2012-05-05
I'm building an update query that will convert the format of data from one field into a new field so that the original can be deleted. This is to make my table more efficient. I am working in only one table and with only two columns, RecordingNum and RecordingNumNew. I already understand how to do the update query, and have run it once for all of the numeric values in the column like so:

UPDATE DocumentLocationInfo SET DocumentLocationInfo.RecordingNumNew = IIf(IsNumeric(RecordingNum),RecordingNum)
WHERE IsNumeric(RecordingNum);

But the remaining data from that column is in this format: "B" & (3 digits) & "P" & (3-4 digits), which means the letter B plus three digits plus the letter P plus 3 or four digits. What I need this concatenation to do is to take out the B and the P, but to add a hyphen between the first 3 digits and the last 3-4 digits, like so: 001-600; 003-296A; 658-654; etc. So:

UPDATE DocumentLocationInfo SET DocumentLocationInfo.RecordingNumNew = {WHAT GOES HERE?}
WHERE IsNumeric(RecordingNum)=False;  'so that it only runs on the non-numeric data

I'm sure there must be some function for this, but I am still fairly new to SQL, especially with concatenation. Any help is greatly appreciated, Thanks!
0
Comment
Question by:littleking4376
[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 3

Assisted Solution

by:R_Janssen
R_Janssen earned 50 total points
ID: 22597254
You'll have to use REPLACE() and such. But I'm not entirely sure if Access has those. A workaround would be to fetch the data, change it and update it. It would take longer and would kinda behave like a cursor in a way.
 
0
 
LVL 18

Assisted Solution

by:mdougan
mdougan earned 50 total points
ID: 22597678

Yea, not sure if Access has the Replace function, but you could give it a try... or they are sure to have something similar.

UPDATE DocumentLocationInfo
SET DocumentLocationInfo.RecordingNumNew = Replace(Replace(RecordingNum, 'B', '-'), 'P', '-')
WHERE IsNumeric(RecordingNum)=False;

I always recommend running the update as a SELECT first, just so you know what you're going to be updating to:

SELECT Replace(Replace(RecordingNum, 'B', '-'), 'P', '-')  AS RecordingNumNew
 From DocumentLocationInfo
WHERE IsNumeric(RecordingNum)=False;
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 50 total points
ID: 22597873
Not an answer:  Access DOES have Replace Function.
0
 

Accepted Solution

by:
littleking4376 earned 0 total points
ID: 22598793
I continued my searching, and found a function that did the trick. This is what I ended up with:

UPDATE DocumentLocationInfo SET DocumentLocationInfo.RecordingNum = Mid(RecordingNum,2,3) & '-' & Mid(RecordingNum,6)
WHERE (((IsNumeric([RecordingNum]))=False));

The only question now is, how do I divide out points to people who tried to help, if I ended up finding my own solution before I had a chance to try theirs?
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

728 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