Lookup

[KatGraham]

HELP! I need to do the following:

Table 1
BeginSeries EndSeries Category
433.10 433.15 1
433.16 433.19 2
433.25 433.28 3

Table 2
Series
433.11
433.20

I need to lookup Table 2 against Table 1 and find the category.

Ie. Output
Series Category
433.11 1
433.20 NULL

HELP???

 

[scriverb]

I create anothe field in Table2 called Category of the same datatype as Category in Table1. Put the following VBA code behind a button in the OnClick event procedure of a new form:
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Table2", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Table1", dbOpenDynaset)
rs1.MoveFirst
Do
rs2.MoveFirst
Do
If (rs1(&quot;Series&quot >= rs2(&quot;BeginSeries&quot) And (rs1(&quot;Series&quot <= rs2(&quot;EndSeries&quot) Then
rs1.Edit
rs1(&quot;Category&quot = rs2(&quot;Category&quot
rs1.Update
rs2.MoveLast
Else
If rs1(&quot;Series&quot < rs2(&quot;BeginSeries&quot And rs1(&quot;Series&quot > rs2(&quot;EndSeries&quot Then
rs1.Edit
rs1(&quot;Category&quot = Null
rs1.Update
rs2.MoveLast
End If
End If
rs2.MoveNext
Loop Until rs2.EOF
rs1.MoveNext
Loop Until rs1.EOF
rs1.Close
rs2.Close
db.Close

This should update your Table2 Category field with the appropriate category from Table1. Bob Scriver