CODEWITHSUNDEEP
sqlms-accesssql-update
st87_top
st87_top

Reputation: 57

Why does my update query to replace string not work?

I have an Access table where I have transaction IDs in the below format:

Transaction_ID
39296165-1
39296165-2
39296165-3
39284029-1
39284029-2

I am trying to write a query which finds the dash and removes the -1,-2,-3 etc., so I can then de-duplicate based on the string before the dash.

I've written the below:

UPDATE mytable
SET Transaction_ID=Left(Transaction_ID,InStr(1,Transaction_ID,"-")-1)*

Which works fine, however, when it comes across a Transaction_ID which doesn't have a dash in the string, it gives me a type conversion and replaces the string with a blank value.

Any advice on error-trapping this?

Upvotes: 0

Views: 272

Answers (2)

HarveyFrench
HarveyFrench

Reputation: 4568

This would also work and would be more efficient.

WHERE Transaction_ID LIKE "*-*"

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Add a WHERE clause to only update if InStr does not return -1:

WHERE InStr(1,Transaction_ID,"-") > 0

Upvotes: 2

Related Questions