Is DLookup with If & And possible? 1

[Eprice]

Hey,
This is very complex and there is probably a better way than a DLookup but I dont know how. I am not getting the correct results with what I have. This is Access XP by the way. I have two tables of course. The first is tblList which I am using in the query. I need to look up the Territory from tblTerritories according to the zip code in tblList.
tblList has
OfficeCode(TX-1) and Zip(79302) that I will use for Dlookup
tblTerritories has
OfficeCode, Territory, StartTerritory and EndTerritory
TX-1 North 790 795
TX-1 South 769 769
TX-1 West 770 789
but the kicker is sometimes a five digit number
TX-5 North 79720 79935

If the Office Codes match then I want the correct Territory that is or falls in between StartTerritory and EndTerritory. I tried to use this below but only get North for all records.

Terrirory: DLookUp("[TerrName]","tblTerritories","[tblTerritories]![OfficeCode] = [OfficeCode]" And IIf(Len([tblTerritories]![StartTerritory]=5),[Zip]>=[tblTerritories]![StartTerritory] And [Zip]<=[tblTerritories]![EndTerritory],IIf(Len([tblTerritories]![StartTerritory]=3),Left([Zip],3)>=[tblTerritories]![StartTerritory] And Left([Zip],3)<=[tblTerritories]![EndTerritory],[tblTerritories]![StartTerritory]=0)))

IF there is an easier way, please let me know. Im sure this will make the report slow. Thanks Lisa

 

[PHV]

you may try this:

DLookUp("TerrName","tblTerritories","OfficeCode='" & OfficeCode & "' AND '" & Zip & "' Between Left(StartTerritory & '000',5) And Left(EndTerritory & '999',5)")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

That works so far. I forgot to mention the default. Any zip codes not listed in tblTerritories should default to Dallas. I need an else to say if it does not match to look for 000 which would return Dallas as the territory. In the table the record would be TX-1, Dallas, 000, 000.

 

[PHV]

Like this ?
Nz(DLookUp("TerrName","tblTerritories","OfficeCode='" & OfficeCode & "' AND '" & Zip & "' Between Left(StartTerritory & '000',5) And Left(EndTerritory & '999',5)"), DLookUp("TerrName","tblTerritories","OfficeCode='" & OfficeCode & "' AND StartTerritory='000' AND EndTerritory='000'"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eprice]

That works!! Thank you, thank you. You guys are great.
Lisa