Simple Querry Help

[tubar]

I Have A Querry That Adds The Amount Of A Certain Light Bulbs I Have Order Since 2003. I Then Subtract The Amount Of That Light Bulb I Have On Hand Now...it Gives Me The Amount I Have Used Since 2003. Now I Want The Querry To Take That Number And Divide It By The Numberber Of Months Its Been When The Querry Was Open.

Example...i Open The Querry 10-23-06
It Tells Me The 60 Watt Bulb
Has Been Ordered 900 Times Since 12-2003
I Have 300 In Stock On 10-23-06
I Have Used 600 Since 12-2003

The Querry Needs To Give Me This Answer
You Use 17 60 Watt Bulbs A Month

 

[Ziggy1]

use this to calculate the months

Expr1: DateDiff("m",[yourdatefield],Now())

 

[tubar]

Not Under Standing

[my Date Feild]

I Assume That Is The Total Amount Of Bulbs Used Since 2003

Again
I Have The Total Bulbs Used Since 2003
I Want To Divide That By The Date The Form Is Open On (in Months)
So I Can Find Out How Many Of That Bulb I Use A Month

 

[gemma-the-husky]

what ziggy is saying is, in your query calculate the months elapsed. In a column put elapsed: DateDiff("m",[yourdatefield],Now()). In this ziggy meant use [your date field] as the initial date you were using. If it is in your table, put the name of the field here. If its a fixed date put #12 Oct 2003# or #12/10/2003# - play with the syntax here, because you might find it treats the second date as 10th December, not 12th October (ie US date format)
The datediff function will calculate the difference in months between the 2 dates.

Now you can use the elapsed field in other formulas. So

(initialstock+inwards-closingstock)/elapsed will give you your monthly usage.

 

[Ziggy1]

thanks Gemma....well said!


Ziggy

 

[tubar]

Now A Enter Parameter Value Box Opens With #12/10/2003#

 

[tubar]

Expr1: DateDiff("m",#12/1/2003#,Now())

NEVER MIND IT WORKS WITH THIS....THANKS AGAIN GUYS SO FOR MY IGNORENCE