Problem with DLookup Criteria Expression

[thefox56]

I cannot get the criteria expression formatted correctly in the following query for a DLookup function. If I hard-code the value I'm searching for, it returns the following results:
Query:
SELECT qryInvenTrans1.[Product ID], qryInvenTrans1.[Product Name], qryInvenTrans1.Onhand, qryInvenTrans1.Category, DLookUp("[CategoryName]","[tblProductCategory]"," 22 Between [StartRange] and [EndRange]&quot AS Cat, IIf(IsNumeric([Category]),"yes","no&quot AS NumCheck
FROM qryInvenTrans1;

Results:
ProdID Product Name Onhand Category Cat NumCheck
022XXX Utility 1 22 Collars yes


When I try to use the field name in the query, I get an error or a data type mismatch or Null. The Category field is a numeric data type and so are StartRange and EndRange.

SELECT qryInvenTrans1.[Product ID], qryInvenTrans1.[Product Name], qryInvenTrans1.Onhand, qryInvenTrans1.Category, DLookUp("[CategoryName]","[tblProductCategory]","[Category] Between [StartRange] and [EndRange]&quot AS Cat, IIf(IsNumeric([Category]),"yes","no&quot AS NumCheck
FROM qryInvenTrans1;


Results:
ProdID Product Name Onhand Category Cat NumCheck
022XXX Utility 1 22 yes

I've also tried the following
SELECT qryInvenTrans1.[Product ID], qryInvenTrans1.[Product Name], qryInvenTrans1.Onhand, qryInvenTrans1.Category, DLookUp("[CategoryName]","[tblProductCategory]","'[Category]' Between [StartRange] and [EndRange]&quot AS Cat, IIf(IsNumeric([Category]),"yes","no&quot AS NumCheck
FROM qryInvenTrans1;


Results:
ProdID Product Name Onhand Category Cat NumCheck
022XXX Utility 1 22 #ERROR yes

Any help is appreciated.