CODEWITHSUNDEEP
sqldatabasems-access-2010
user1781966
user1781966

Reputation:

Updating parts of a record in Access

Is there a way to update only parts of a database record? I have a table that lists a bunch of different items followed by their cost, but I would like to remove the cost from the item. For example:

Current table looks like this

---Item-------------
  Apple- 1.35
  Orange - 1.24
  Grape - 2.00
  ETC..
---------------------

I would like to update the table with the same records, but without the hyphened price at the end. There are hundreds of different records in this table, so I can't just update by a specific record. I've tried using wildcards, but I wasn't able to get the results I'm looking for. Is there a way of doing this?

Upvotes: 1

Views: 54

Answers (3)

jpw
jpw

Reputation: 44931

If the part you want to remove always begins with a hyphen, and there won't be any hyphens as part of the item name then this code should do what you want:

update YourTable set item = left(item, instr(item,"-")-1)

Before you run the update you might want to try it as a select:

select left(item, instr(item,"-")-1) as newitem from YourTable

If your item name can contain hyphens maybe searching for a hyphen followed by a space would work: "- "

Also, a where clause should probably be used to avoid trying to update rows without the price part.

SELECT Left(item,InStr(item,"- ")-1) AS newitem
FROM YourTable
WHERE InStr(item,"- ") > 0;

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

I think I would go for Mid:

SELECT t.ExistingField, 
  Trim(Mid([existingfield],1,InStr([existingfield],"-")-1)) AS NewData
FROM Table t

So:

UPDATE Table 
SET Table.UpdatedField = Trim(Mid([existingfield],1,InStr([existingfield],"-")-1))
WHERE Table.ExistingField Like "*-*"

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180958

Get the ParseWord function, stick it in a module, and reference it in your UPDATE query like so:

ParseWord([NameOfYourField], 1)

You can also use the Split() function to do the same thing.

Upvotes: 0

Related Questions