Query between 2 fields in a table

[smc678]

Im trying to figure out how to return values in a query when the criteria looks at a table between 2 fields like Between [TBLBuildingLocations]![IBST1] And [TBLBuildingLocations]![IBST2] but im getting an error. any ideas??

[June7]

What is the error?

Post the entire sql statement.

[smc678]

Well its not and error its like it cannot reconize that table field combo.

SELECT TBLAgedLPN.LOCN, TBLAgedLPN.AGE, TBLAgedLPN.LPN, TBLAgedLPN.ASN, TBLAgedLPN.[DOLLAR VALUE], TBLAgedLPN.[VENDOR NAME], TBLAgedLPN.[LAST TRANSACTION]
FROM TBLAgedLPN
WHERE (((TBLAgedLPN.LOCN) Between [TBLBuildingLocations]![IBST1] And [TBLBuildingLocations]![IBST2])) OR (((([TBLAgedLPN].[AGE]) Like "Lost") Like "DM 001"))
ORDER BY TBLAgedLPN.LOCN, TBLAgedLPN.ASN;

[June7]

Can't reference another table that way. TBLBuildingLocations is not included in the query.

Do TBLBuildingLocations and TBLAgedLPN have a relationship?

[smc678]

No they don't. What I'm trying is say you have a form it has 2 fields the 1st field has 1 the 2nd has 100. In the query I'm using the criteria section to return all values that are between 1 and 100 looking at this form. This works but I'm trying to do the same thing with a table. In my case IBST1 has a value of 1 and IBST2 has the value of 100 this doesn't work. I would just use the form since I know it works but I can't open the form every time and it only works if the form is open.

[June7]

That really makes no sense. Entering criteria into controls of a form and referencing those controls in query as dynamic parameters is not the same as referencing the entire field of a table.

These tables must have some kind of association. Need to know more about data structure.

[smc678]

So is it possible to reference the form when the form is closed?

[June7]

No, it is not.