Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update query to replace everything after comma

Status
Not open for further replies.

ali32uk

Technical User
Mar 31, 2011
22
GB
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
 
Adding WHERE may suit:

Code:
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

Code:
WHERE [manufacturer_part_number] LIKE "*-*"

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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top