return a value from a range of values

[Randy]

I have one table with my account listing i.e.
1200000
1470000
2100000
3505000
etc etc

in another table I have the default profit center range (line of business) that each account will use if an end user does not code the journal entry correctly. i.e.
acctfrom acctto pc
0000000 1000000 1000
1000001 1999999 1000
1100000 1100000 2000
1100001 1299999 9298
etc etc etc

so I have a query that brings in the accounts from table 1 and shows the default PC in table2

iif([table1]![acct]=between([table2]![acctfrom],[table2]![acctto]),[table2]![pc],)

I do not get a correct answer set. is there a better function to use than IIF and BETWEEN. Or can anyone see the error in my current statement.

 

[FoFa]

Try:
Dlookup("pc","table2","'" & [Table1].[acct] & "' between [table2].[acctfrom] AND[table2].[acctto]")

Assumes acct are text data types.

May have to adjust syntax, just off the top of my head.