Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-09-29
4
Medium Priority
?
227 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 200 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 200 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 200 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

927 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