By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

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

newnewbie
P: 54
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

Expand|Select|Wrap|Line Numbers
  1.  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!
Dec 20 '06 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
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

Expand|Select|Wrap|Line Numbers
  1.  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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As Database, MyRS As Recordset
  2. Dim MyDB_2 As Database, MyRS_2 As Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyDB_2 = CurrentDb()
  6.  
  7. Set MyRS = MyDB.OpenRecordset("Claims")
  8. Set MyRS_2 = MyDB_2.OpenRecordset("Raw Data")
  9.  
  10. Debug.Print MyRS.RecordCount
  11. Debug.Print MyRS_2.RecordCount
  12.  
  13. Do While Not MyRS_2.EOF
  14.   Do While Not MyRS.EOF
  15.    Debug.Print MyRS_2![Batch] & " <==> " & MyRS![Name]
  16.     If Trim(Left(MyRS_2![Batch], 18)) = Trim(Left(MyRS![Name], 18)) Then
  17.       Debug.Print "Found a Match at Batch Number: " & MyRS_2![Batch]
  18.         Exit Sub
  19.     End If
  20.     MyRS.MoveNext
  21.   Loop
  22.   MyRS.MoveFirst
  23.   MyRS_2.MoveNext
  24. Loop
  25.  
  26. MyRS.Close
  27. MyRS_2.Close
Dec 21 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Left([RawData].[Batch], 18) As Batch18
  2. FROM Claims, RawData
  3. WHERE Left([RawData].[Batch], 18) Like Left([Claims].[Name], 18)
  4. GROUP BY Left([RawData].[Batch], 18);
  5.  
Mary
Dec 21 '06 #3

newnewbie
P: 54
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As Database, MyRS As Recordset
  2. Dim MyDB_2 As Database, MyRS_2 As Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyDB_2 = CurrentDb()
  6.  
  7. Set MyRS = MyDB.OpenRecordset("Claims")
  8. Set MyRS_2 = MyDB_2.OpenRecordset("Raw Data")
  9.  
  10. Debug.Print MyRS.RecordCount
  11. Debug.Print MyRS_2.RecordCount
  12.  
  13. Do While Not MyRS_2.EOF
  14.   Do While Not MyRS.EOF
  15.    Debug.Print MyRS_2![Batch] & " <==> " & MyRS![Name]
  16.     If Trim(Left(MyRS_2![Batch], 18)) = Trim(Left(MyRS![Name], 18)) Then
  17.       Debug.Print "Found a Match at Batch Number: " & MyRS_2![Batch]
  18.         Exit Sub
  19.     End If
  20.     MyRS.MoveNext
  21.   Loop
  22.   MyRS.MoveFirst
  23.   MyRS_2.MoveNext
  24. Loop
  25.  
  26. MyRS.Close
  27. MyRS_2.Close
Dec 21 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.