Help with Partial string Search

[victor64]

Hello,

I'm try to update data a column in the IDENTIFICATION_DATA based on partial match the AOP5 table. The problem is I don't know how to parse the string after 4 spaces and only consider 11 characters, for example the data in the identification table is in the following format: 00-028-1234. The data in the AOP5 table is in the following format 1305-00-028-1234(1). I only want to update insert data in the identification table when 00-028-1234 matches in both tables.

Thanks

Victor

Code:

Dim mySQL_Statement As String = "UPDATE IDENTIFICATION_DATA " & vbNewLine & _
" INNER JOIN " & vbNewLine & _
" aop5 " & vbNewLine & _
" ON IDENTIFICATION_DATA.NIIN " & _
" = aop5.NSN " & vbNewLine & _ ''''how do i modify this line to only search for data after the fifth position and the next 11 characters??
" SET IDENTIFICATION_DATA.NARC = " & _
" aop5.sn "

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

'data adapter
Dim objDataAdapter As New OleDb.OleDbDataAdapter(mySQL_Statement, objConnection)

''dataset object
Dim objDataSet As New DataSet
' 'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDataSet, "SN")
objConnection.Close()
MsgBox("Completed")

 

[MattP]

Didn't I answer this one already?

SELECT *
FROM TableA a JOIN TableB b ON SUBSTRING(a.column, 5, 11) = b.column

 

[victor64]

Hello,

I'm trying to update the value in tableA, therefore I need to use the insert and update command, I tried to modify my code using your syntax but it still doesn't work. In this case table A is identification table and table B is AOP5. The error message states the substring is not recognized.

Dim mySQL_Statement As String = "UPDATE IDENTIFICATION_DATA " & vbNewLine & _
" INNER JOIN " & vbNewLine & _
" aop5 " & vbNewLine & _
" ON IDENTIFICATION_DATA.NIIN " & _
" = substring(aop5.NSN,5,11) " & vbNewLine & _
" SET IDENTIFICATION_DATA.NARC = " & _
" aop5.sn "

 

[MattP]

Jet should recognize SUBSTRING.

UPDATE id
SET id.NARC = aop5.sn
FROM IDENTIFICATION_DATA id, aop5 ao
WHERE SUBSTRING(ao.NSN, 6, 11) = id.NIIN

 

[victor64]

I tried your approach with the following code, but I'm getting a "Query expression error on the AOP5.sn line. Any idea why it's not working?

Code:

Dim mySQL_Statement As String = "UPDATE ID" & vbNewLine & _
" SET ID.NARC = AOP5.sn " & vbNewLine & _
" from IDENTIFICATION_DATA, ID, AOP5 ao where SUBSTRING (ao.NSN, 6, 11) = id.NIIN"

What is the purpose of including ID and ao in the query string? Sorry I don't quiet follow the logic.

Thanks,

Victor

 

[cjard]

What is the purpose of including ID and ao in the query string? Sorry I don't quiet follow the logic.

ID is an alias for the IDENTIFICATION_DATA table(, and it is used)
AO is an alias for the AOP5 table, but the alias is not used so there is no point in having it

I thought Access used MID() for substringing, not SUBSTRING

 

[MattP]

ID is an alias for the IDENTIFICATION_DATA table(, and it is used)
AO is an alias for the AOP5 table, but the alias is not used so there is no point in having it

I thought Access used MID() for substringing, not SUBSTRING

Oops used ao in the WHERE clause but not SET

Access does use MID(). I gave him an example here

 

[victor64]

I got it to work with the Mid function, Thank You very much for your Help.

Victor