I would like to write some code that

Opens a rs (a query in access)

Loop thru each record in table one

For each record in table 1, I want to compare a field (CS which is a number
Cross section) to another table (table2) which has a range values for CS

Table 2:
Ref CSFrom CSTo
M-100 0 100
M-200 100 200
M-300 200 300
etc

I assume I would need to loop thru each record in TABLE 2 to find the range
where CS From TABLE1 is BETWEEN CSFrom and CSTo in TABLE2.

Then I want to update a field in TABLE1 with the value from TABLE2.

Can someone help me write this?  Am I approaching this correctly?

Thanks in advance

ave schrieb:

First of all: This might cause wrong data.
Which one of the M-x00 values will be assigned, if CS is exactly 100 or 200 ?

Maybe. But why don't you use SQL ?

Let's assume, you try to update the field NewValue in Table1 with the
Value of Ref in Table2.

Try this:

DoCmd.RunSQL "UPDATE Table1 SET NewValue = [Table2].[Ref] " &
"WHERE ([Table1].[CS]>[Table2].[CSFrom]) And ([Table1].[CS]<[Table2].[CSTo])"

This is one instruction only: Quick, easy, clean, fast, nearly no VBA
programming  and running on each SQL Database.

Ralf.

Well, it can be done MUCH more simply: no VBA code at all, just a
simple Update query (albeit one using the rather obscure Non-Equi Join
feature). Or by using a similar Select query, you may not need to do
it at all!

UPDATE Table1
INNER JOIN Table2
ON Table1.CS > Table2.CSFrom AND Table1.CS <= Table2.CSTo
SET Table1.fieldname = Table2.Ref;

Or, just make the above into a Select query and display Ref directly
from the query without storing it redundantly.

<newsgroups trimmed therefore posted & emailed - please reply to the
group>

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public