DLookup between dates and other criterias

[robsworld78]

Hi, I've been struggling trying to get a DLookup to work. I have a report that I want to look up some data.

I'm trying to make a report that will print an old invoice based on a date and employee and show the correct inventory levels at that time. Right now I have it working so a person can print todays invoice however once new data is entered I can't get the lookup working for older data.

I'm tracking inventory and have 2 bound fields, called "given" and "sold", everyday when inventory is given to an employee its entered in a subform and at the end of the day the sold items are entered in as well. After the day is over the employee retains his/her inventory for the next day and more is added to it. So a balance of the inventory is kept and works fine.

I have an invoice created which shows how much of each product the select employee had at the beginning of the day and how much they sold and then how much is remaining.

Here's the DLookUp I use in my invoice report and on a form to give me the current inventory balance for a certain employee and product.

=DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU]")

Here's the Dlookup I use in my invoice report to give me the amount the day was started with.

=DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU]")+[Sold]

Those both work fine but now I'm trying to add a "between date" criteria to the DLookup as well. I made a form which asks for an employee and date to lookup the invoice however it only brings today's inventory balances even when I select a date from a week ago. I'm hoping I can have the date that's entered in the form reflect on the Dlookup.

So I want the Dlookup to look up the same data I have it set to but only lookup that data from the beginning to the date selected in the form that opens the report not to todays date. The beginning date will always be the same obviously it just needs to stop at the date I select when I runt he report so my balances are for that date not today.

I hope all this makes sense. I've tried so many different variations of Dlookup including the date but can't get it to only pull the data up to the date and do the math up to that point.

Any help is greatly appreciated.

 

[pdldavis]

=DLookUp("sum(Given-Nz([Sold],0))","Product Query","[EmployeeID] = [Reports]![Invoice Old]![EmployeeID] And [SKU] = [Reports]![Invoice Old]![SKU] And [dateSold] between me.txtStartDate and me.txtEndDate")

Something similar to the above should work.