Solved

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

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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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: …

630 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