Update query to replace everything after comma

[ali32uk]

Hi

I m using a update query to delete everything in a column which is after the last "-". the code is as below

The trouble I m having is that some of the fields don't have any "-" in them and so it deletes the whole entry which is not what I m looking for

UPDATE [PRODUCTREGISTER SCP] SET [PRODUCTREGISTER SCP].Manufacturer_Part_Number = Left([PRODUCTREGISTER SCP].[Manufacturer_Part_Number],InStr([manufacturer_part_number],"-")-1)


Can anyone help with this?

Ali

 

[Remou]

Adding WHERE may suit:

UPDATE [PRODUCTREGISTER SCP] SET [PRODUCTREGISTER SCP].Manufacturer_Part_Number = Left([PRODUCTREGISTER SCP].[Manufacturer_Part_Number],InStr([manufacturer_part_number],"-")-1)
WHERE InStr([manufacturer_part_number],"-") > 0

Or using WHERE with LIKE

WHERE [manufacturer_part_number] LIKE "*-*"

http://lessthandot.com

 

[ali32uk]

Great thank you had to make a few changes to existing where statement, but worked great