?
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
?
230 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

589 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