I'm working on an access database for a software engineering course in my masters. Basically what I'm doing is taking the Microsoft template database called "Orders management database" and extending it to meet my needs and to fix some of the table design. The system will be an order tracking database with some extra fields added to create good business reports in terms of profitability per product, things like that.

One of the issues with that database as it is is that it stores price information in the orderdetails table. I want to get rid of any instances of storing data when that data can be calculated.

So what I've done is create a table called "Pricedetail" it has these fields-

PricedetailID : the key
ProductID : has a many to 1 relationship with the Products table
UnitPrice
PriceStartDate
PriceEndDate

So a single ProductID can have multiple entries in the PriceDetail table. This will allow for product prices to be changed, and historic data is maintained about product pricing. It will also allow for me to calculate old invoice totals instead of storing them. When an invoice is calculated I'll do a between on the invoice date and PriceStartDate and PriceEndDate..

So here is the problem. I'm familiar with using access to process data that already exists in tables. I'm not so familiar with using Forms to enter that data.

I have a Add Order main form which contains a Order detail subforms. The OrderID in the main form is linked to the orderID in the child form. So when you are looking at order #14 it only shows the products that exist in the order details table for order #14. What I'm having problems with is looking up the price from the pricedetail table.

I've used dlookup like this- DLookUp("UnitPrice","PriceDetail","ProductID = " & [ProductID])

Which works, but it doesn't take my date calculations like explain above into consideration. Also it returns an #error? on the last record that is null. I know I can use IFF or NV to overcome this, but not sure of the exact syntax.

I've also read some help manuals that saying using dlookup is not the best way to do this, and it should be done with a query.

So in my query I put the Orders detail table and Price detail table with the productID joined between the two, and I put my between in the criteria for the field "order date". And the query works if I click it and enter the date manually. However, when I set the query to be the Unit Price control source in my form I get a #name? error. If I can get the dlookup syntax nailed down properly I don't even have to mess with a query as I think the performance problems cause by dlookup will be minimal as there are only going to be 10 or 15 product fields for each order at most.

Any help would be appreciated. I'm willing to send the database over to somebody that has a good solution and not just wanting to look at it.