Function in Sql Statement/ Query builder

[pcdaveh]

I'm trying to Running a query that needs find one of the strings below,

Strings to search for in the Reference column of TableA "PD", "03", "CD", "PR", "04"
PD needs to return a 1 in the TransActionID Column
03 2 When len([Reference]) <= 2
CD 3
PR 4
04 5 When len([Reference]) <= 2

none of the above 0

Updating the TransactionId to the return value.

TableA

Reference TransactionID
13012901pd 0
13032901PD 0
13032901PDLISAM 0
13032901PDLISAM 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PDLISA 0
13012901PD 0
13032901PD 0
13032901PD 0
13032901PDLISA 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
13012901PD 0
13032901PD 0
15010401PD 0
15010401PD 0
15010401PD 0
15010401PD 0
03 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CED 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
013001CD 0
25032001CD 0
27010201PD 0
27010201PR 0
27011601PD 0
27021501pd 0
27030801PD 0
27010201PD 0
27010201PR 0
27011601PD 0
27021501PD 0
27030801PD 0
27010201PD 0
27010201PR 0
27011601PD 0
27021501PD 0
27030801PD 0
27010201PD 0
27010201PR 0
27011601PD 0
27021501PD 0
27030801PD 0
27010201PR 0
27010201PD 0
27011601PD 0
27021501PD 0
27030801PD 0
04 0
04 0

 

[jjonesal]

Create an update query and add this expression to the update to line of the query for the transaction field:

IIf(Len([Reference])<=2,IIf(Left([Reference],2)=&quot;03&quot;,2,IIf(Left([Reference],2)=&quot;04&quot;,5,0)),IIf([Reference] Like &quot;*PD*&quot;,1,IIf([Reference] Like &quot;*CD*&quot;,3,IIf([Reference] Like &quot;*PR*&quot;,4,0))))

Hope this helps... J. Jones
jjones@cybrtyme.com

 

[jjonesal]

The SQL Stmt would be:

UPDATE Tablename
SET Table3.TransID = IIf(Len([Reference])<=2,IIf(Left([Reference],2)=&quot;03&quot;,2,IIf(Left([Reference],2)=&quot;04&quot;,5,0)),IIf([Reference] Like &quot;*PD*&quot;,1,IIf([Reference] Like &quot;*CD*&quot;,3,IIf([Reference] Like &quot;*PR*&quot;,4,0))));
J. Jones
jjones@cybrtyme.com