Criteria trouble with Query and DLookup (1 Viewer)

[vowelsounds]

I am super new to Access, and I've faced this problem for a while. I'm trying to populate a text box with the result of using the DLookup function on a query. I'm working on building up a little rental database, but we have weird rental rates with a 3 day minimum, 5-day week, and 3-week month. I have a few tables and forms.

Tables:
Products - contains all products with their d/w/m rental rates
Rates - contains 4 fields (rental duration in days -- up to the max 180 days -- and the number of days, weeks, and months charged for that duration)
Rental Records - contains data on salesmen, location, customer, shipping etc for each rental transaction
Rental Record Details - contains a list of each item that is rented out, the rental record id for that rental, rent out date, return data, and quantity for the item

Forms:
New Rental - lets the user select the salesman, location, customer, shipping info, etc. and has one subform in which the user selects the rented items from a list (Products) and inputs quantity, rent out date and return date

I made a query (Cost Query) to calculate a few things for each item that is rented out:

Duration: DateDiff("d", [Rental Record Details].[Rent Out] , [Rental Record Details].[Return])

Cost: [Rental Record Details].[Quantity] *
([Products].[Price - Day]*[Rates].[Days] +
[Products].[Price - Week]*[Rates].[Weeks] +
[Products].[Price - Month]*[Rates].[Months])

When I ran this initially, it returned the costs for all possible durations. So I added the following criteria:

DateDiff("d",[Rental Record Details].[Rent Out],[Rental Record Details].[Return]) = [Rates].[Duration]

equating the actual rental duration with the appropriate duration in Rates.

All this works great, but on the subform, I am trying to add a text box that is populated with the Cost (query calculation) for each item. I used

DLookup("[Cost]", "Cost Query")

This returns the (I don't know how else to explain this) results from the query before I added the criteria. I've tried adding the same criteria as above, but it just returns an error.

I apologize for the lengthiness and any confusion. Any help with this problem would be greatly appreciated!

 

[Minty]

I may have misunderstood - but if the subform has it's record source as the Cost query simply set the control to use the query field?

 

[vowelsounds]

I may have misunderstood - but if the subform has it's record source as the Cost query simply set the control to use the query field?

I tried this; the only thing it returns is #Name?

 

[Minty]

Okay can you post the SQL of the query ?