Assign value based on field between diff values 1

[DajTwo]

I know how to do this with a series of Iif statements but I would like to do this where I can change the 'borders' in a table

Depending on the value of a $ field [$field], I want to assign an [id] depending if [$field] is between 0 to 101, 100 to 200, 201 to 300 etc..

The reference table would look like [id], [lowerborder], [highborder].

How can I put the border in a table instead of in the IIf statement?

Thanks

 

[PHV]

A starting point (SQL code):
SELECT *
FROM yourTable INNER JOIN tblBorder
ON yourTable.[$field] BETWEEN tblBorder.lowerborder AND tblBorder.highborder

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Thanks for the tip. I appreciate!!

 

[DajTwo]

BTW IS there a way to do the same in VBA?

 

[PHV]

You may use the DLookUp function against a query, or use a Recordset.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

Thanks again for the line of direction.. Ill play around and come back if Im stuck