help with a simple access update query

[john6767]

Hi guys,
i'm new to access, have done a little sql before and am trying to to sort out a table i have been given. It has in one column 'productBrandroductCode' and i am trying to split the data that is seperated by the ':' up into two seperate columns, brand and code.

I have been typing the sql into access because its gui is hard to use (for me anyway!) and i'm not getting a result. What is the sql for this? i have been using the following, i was going to run the query for every brand.. maybe theres a better way?

UPDATE products
SET brand = brandName1
WHERE item Like "*brandName1*"

by the way, this query doesn't seem to work in access.

 

[john6767]

ok, i'm stupid, i just worked access out, the sql does actually work i was viewing not running the query, but still this will only get the brands into another table, how would i grab the info after the ':' and put that into another column? is this possible? i'm not too keen on updating a 5000+ entry database one by one??

thanks

 

[PHV]

Something like this ?
UPDATE products
SET brand=Left(item,InStr(item,':')-1)
,code=Mid(item,InStr(item,':')+1)
WHERE item Like '*:*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[john6767]

perfect, that saved me a whole heap of time, cheers!