DLookup matching select characters from one table to select characters in another

[newnewbie]

I need help with a query where I am trying to match and display the value (First 18 characters of it) in one table that matches the first 18 characters of the field value in another table. Tables are not linked.

Tables, fields and data examples:
Table # 1 Name: Claims
Filed Name: Name
Field Value example: 2006120800011CLT108.TIF#http://fl-jax-sharp/sites%2Fclaims%2FClaims%2F2006%...and it goes on

Table # 2 Name: Raw Data
Field Name: Batch
Filed Value example: 2006120800011CLT108.TIF

 DLookup ("[Batch]", "[Raw Data]", "[First 18 characters of Batch Field]"="[First 18 characters of Name Field]", "[Name]", "[Claims]"

I don't know how to write out the "[First 18 characters of Name Field]"...maybe I even need to use a different function altogether....

Please advise,
Thanks!

[ADezii]

I need help with a query where I am trying to match and display the value (First 18 characters of it) in one table that matches the first 18 characters of the field value in another table. Tables are not linked.

Tables, fields and data examples:
Table # 1 Name: Claims
Filed Name: Name
Field Value example: 2006120800011CLT108.TIF#http://fl-jax-sharp/sites%2Fclaims%2FClaims%2F2006%...and it goes on

Table # 2 Name: Raw Data
Field Name: Batch
Filed Value example: 2006120800011CLT108.TIF

 DLookup ("[Batch]", "[Raw Data]", "[First 18 characters of Batch Field]"="[First 18 characters of Name Field]", "[Name]", "[Claims]"

I don't know how to write out the "[First 18 characters of Name Field]"...maybe I even need to use a different function altogether....

Please advise,
Thanks!

'This should solve your problem nicely:

Dim MyDB As Database, MyRS As Recordset
Dim MyDB_2 As Database, MyRS_2 As Recordset
 
Set MyDB = CurrentDb()
Set MyDB_2 = CurrentDb()
 
Set MyRS = MyDB.OpenRecordset("Claims")
Set MyRS_2 = MyDB_2.OpenRecordset("Raw Data")
 
Debug.Print MyRS.RecordCount
Debug.Print MyRS_2.RecordCount
 
Do While Not MyRS_2.EOF
  Do While Not MyRS.EOF
   Debug.Print MyRS_2![Batch] & " <==> " & MyRS![Name]
    If Trim(Left(MyRS_2![Batch], 18)) = Trim(Left(MyRS![Name], 18)) Then
      Debug.Print "Found a Match at Batch Number: " & MyRS_2![Batch]
        Exit Sub
    End If
    MyRS.MoveNext
  Loop
  MyRS.MoveFirst
  MyRS_2.MoveNext
Loop
 
MyRS.Close
MyRS_2.Close

[MMcCarthy]

ADezii's answer is correct. However, if you just need a basic query. As long as there aren't too many records the following should work.

SELECT Left([RawData].[Batch], 18) As Batch18
FROM Claims, RawData
WHERE Left([RawData].[Batch], 18) Like Left([Claims].[Name], 18)
GROUP BY Left([RawData].[Batch], 18);
 

Mary

[newnewbie]

ADezii,
Forgive me my ignorance, but if I only knew where to put the code you wrote for me. I know the question sounds dumb, but I am not a programmer and I just do basic queries in the design view...So if you could tell me where I insert the Code...

Thanks...

'This should solve your problem nicely:

Dim MyDB As Database, MyRS As Recordset
Dim MyDB_2 As Database, MyRS_2 As Recordset
 
Set MyDB = CurrentDb()
Set MyDB_2 = CurrentDb()
 
Set MyRS = MyDB.OpenRecordset("Claims")
Set MyRS_2 = MyDB_2.OpenRecordset("Raw Data")
 
Debug.Print MyRS.RecordCount
Debug.Print MyRS_2.RecordCount
 
Do While Not MyRS_2.EOF
  Do While Not MyRS.EOF
   Debug.Print MyRS_2![Batch] & " <==> " & MyRS![Name]
    If Trim(Left(MyRS_2![Batch], 18)) = Trim(Left(MyRS![Name], 18)) Then
      Debug.Print "Found a Match at Batch Number: " & MyRS_2![Batch]
        Exit Sub
    End If
    MyRS.MoveNext
  Loop
  MyRS.MoveFirst
  MyRS_2.MoveNext
Loop
 
MyRS.Close
MyRS_2.Close