Add prefix to existing value using update

[zzz999]

I have a table with a column named SKU (text). I need to prefix SOME of the records in this column with a 3 digit number.

Can someone please provide me with the update code to retrieve the current value and prefix three digits to this value.

I am using Acess 2000

TIA,

Steve

 

[SgtJarrow]

Sounds to me like and IIf situation....

IIf(enter your criteria here...[SKU]="something","your three digits & [SKU],[SKU])

This says....If [SKU] = some criteria, then make [SKU] = "your three digits" and [SKU], else make [SKU] = [SKU]

For Example IIf([SKU]="12*", "000" & [SKU], [SKU]) would give:
SKU: 12345 00012345
SKU: 34567890 34567890

If you need more help, please le me know. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[zzz999]

Robert,

Thanks for you help. I've just realised I left off a couple of important points.

1) I need to a SQL UPDATE as the database is located on our web server. Can you help?

2) I need to remove the first two characters of the SKU, prior to adding the new prefix.

TIA,

Steve

 

[SgtJarrow]

Sure, but if you can provide a little more info, it will be much easir to see what you need exactly.

1. Is there a set format for the SKU???
2. What is the criteria that says you need to modify the SKU??? (My example is if the SKU begins with alpha characters).
3. Is the "prefix" a set number??? If so, what is it???

I am imagining the problem with the below sample data...
Current Needs to read
HG67890 0067890
RJ87654 0087654
7657687 7657687
8798765 8798765

If you can provide me some examples, I can solve this to your exact specs. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[zzz999]

Richard,

The format of the SKU actually changes depending on the vedors SKU. The reason I need to prefix our code to these SKU's so our bookeeping department can seperate them easily.

For the most part the SKU will begin with two to three characters. A couple of examples are:

HB197.63N.097 needs to become 013-197.63N.097
865412 needs to become 054-865412

As you can see some SKU's need to have characters removed prior to adding the prefix. Others just need to prefix adding.

I realize this will require multiple updates to the table.

Really appreciate you helpon this.

TIA,

Steve

 

[SgtJarrow]

Ok,
That helps a lot.....and by the way, this is not too difficult a problem.

One follow-up question before I can go on this....
How many vendors are yuo talking about??? Do you have a Vendors table??? If so, is there a field (or can you add one) that lists the prefix for each vendor???

Here's what I am envisioning....
If SKU begins with letters, remove letters and make SKU =
Vendor Prefix & "-" & Remainder of SKU
If SKU begins with numbers, make SKU =
Vendor Prefix & "-" & SKU

Will this fit into EVERY scenario???? In other words, will this work for EVERY record in your database????

If so, I can create a function to do this for you.....not a tough problem really.
"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[zzz999]

we don't have a vendor table because a many products come through a single supplier, so presently that is how they are seperated.

What you propose should work in most instances. As I said before I don't need a single statement to cover all eventuallities as I have aleardy done some of the products manually and a few in the new format when we recently added some new items.

I just need the ability to select a specific product line and modifiy the SKU. For example:

Get substring(SKU,1,2)
If substring = "HB"
newstring = "013-" & substring(SKU,3)
Update SKU
End if

Hopefully that makes sense. Just need to take this and comvert it to SQL!

Thanks,

Steve

 

[SgtJarrow]

I created a small table with a few entries....some that began with numbers and some that began with two letters. I than created the below query that lloked for anythign that began with two lettersand updated it by removing the letters and adding "013-". This is as close as I can get for you, but you can probably tweak it as necessary to make it work. Below is the SQL for the qeury. Hope it is what you need....

UPDATE Table1 SET Table1.sku = "013-" & Mid([Table1]![sku],3)
WHERE (((Table1.sku) Like "[A-Z]*&quot);
"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com