Query help needed using values from current form

[iainmid]

Hi i am having problem creating a query that will pull the price of a property into the price field on to a booking order form depending on the values of the start date and property number enter onto the same form.
i have a two tables property price and booking order.

Fields in the property price are property price no, property no, start date, end date and price.

Fields in the booking order table are booking no, property no, start date, end date, cost, no of days, total cost.

When in the booking order form i need the query to get the price from the property price table matching on the property no i've selected on the booking order form and also the start date i've entered on the booking order form needs to be between the start and end date fields in the property price table.

This is the query i have created but doesn't work
SELECT [Property Price].Price
FROM [booking order] INNER JOIN [Property Price] ON [booking order].[Property No] = [Property Price].[Property No]
WHERE ((([forms]![booking order subform]![start date]) Between [property price].[start date] And [property price].[end date]) AND (([Property Price].[Property No])=[forms]![booking order subform]![property no]));

Thanks for your help

 

[pbaldy]

Define "doesn't work". If that's actually a subform, as its name implies, then your reference to it is wrong. Here's a handy reference:

http://www.mvps.org/access/forms/frm0031.htm

 

[iainmid]

Hi Paul

Yeah the booking order form is a subform for a booking form which is not need for this query so i didn't forgot to mention that the booking order was a subform.
The query itself fails to show any value.