Solved

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

Posted on 2008-09-29
4
213 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
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now