find out values between two dates column

[learning_graccess]

I have four columns like [start date], [end date], [item], [quantity]. Values are like

1st Row >> [1 April 2012], [30 April 2012], pen, 30;
2nd Row >> [5 April 2012], [12 April 2012], pencil, 75
and etc.

It means from 1st April to 30th April we supplied each day 30 quantity of pen; and from 5th April to 30th April each day we supplied 75 qty of pencil

Now if I want to find out total qty we supplied on 7th April, I wonder how I can find out that?

But I know he answer should be [30 + 75 = 105] in the above example.

[R_Badger]

The SQL for the query would be something like

SELECT sum([quantity]) as [Total_qty] FROM [SOURCE_TABLE] WHERE [START DATE]<#"&TEST_DATE&"#" and [end date]>#"&TEST_DATE&"#"

Replace [SOURCE_TABLE] with your table name
Replace [TEST_DATE] with your flavor of vba variable or form control (to use a static string value remove the "&[TEST_DATE]&" i.e. #07/04/2012#

[learning_graccess]

thanks
but it does not calculate actual quantity.
startdate=1, enddate = 3, qty=50 means from 1st to 3rd March each day we supplied 50 qty...

So when I find out for 2nd March, it should include this 50 also though 2 is not in start or end date in that row. I don't know about vba or form control

[R_Badger]

I hate to tell you this but 1 is not a date it is a number, and on its own, 1 does not mean 1st march, it means one.A date for the first of march would be 01/03/2012, and the end date would be 03/03/2012 (beware i use UK date format DD/MM/YYYY)Therefore you should be testing for 02/03/2012.1,2,3,4,5,6 etc are numbers and not dates, in your example you provided actual dates, so i presumed the data being held was actually dates.