Query on each years savings 2

[Tmat]

I forgot to add usage.<br>I am trying to create a report that shows the&nbsp;&nbsp;savings each year from our programs. I am using <br><br>Program Start Date<br>Program End Date <br>Monthly Usage<br>Current Cost <br>New Cost<br><br>I have tried a number of different queries with the Datepart function but I dont get the right answer. Some of the programs run a few months and some a few years. My results are grouping my programs by year not my savings by year. I hope this is clear. I would appreciate any help.<br><br><br>

 

[rochelle]

Sorry but it's not clear.&nbsp;&nbsp;Give some sample data and what the report looks like.

 

[Tmat]

I am trying to determine my yearly savings from the following table. I need to run a report periodically to give us savings year to date, just for the current year.<br><br><br>prop¦category¦Item¦Start date¦Enddate¦OldCst¦NwCst¦MthUsage <br>X&nbsp;&nbsp;&nbsp;¦Food....¦Shrimp¦3/1/00..¦2/30/01¦5.....¦3....¦1000 <br>Y&nbsp;&nbsp;&nbsp;¦Food&nbsp;&nbsp;&nbsp;&nbsp;¦Tbone ¦1/1/00&nbsp;&nbsp;¦5/30/00¦2.9&nbsp;&nbsp;&nbsp;¦2.5&nbsp;&nbsp;¦2000 <br>X&nbsp;&nbsp;&nbsp;¦Bev&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;¦Wine&nbsp;&nbsp;¦5/1/99&nbsp;&nbsp;¦4/30/01¦75&nbsp;&nbsp;&nbsp;&nbsp;¦70 &nbsp;&nbsp;¦200 <br><br>So when I run a report for the year 2001. I only want to capture the 2 months of the shrimp program and 4 months of the wine program. And&nbsp;&nbsp;vise versa for the year 2000 I only what to capture the usage in that year. Its 12 months&nbsp;&nbsp;I can not figure how to break it out. Maybe my approach is what’s wrong? I don’t know. I am stuck. Again any help would be appreciated.<br>

 

[rochelle]

I created a table called Table1 based on your data.&nbsp;&nbsp;Then I created a form called AskYear with a textbox in it called ReportYear and I typed the year number into the textbox.&nbsp;&nbsp;I added two other textboxes called BOY (beginning of year) whose controlsource is =DateSerial([ReportYear],1,1) and EOY (end of year) whose controlsource is =DateSerial([ReportYear],12,31)<br><br>To see the solution, go to the SQL view of a new query and paste the following in then switch to design view :<br><br>SELECT IIf([StartDate]&lt;[Forms]![AskYear]![BOY],[Forms]![AskYear]![BOY],[StartDate]) AS CalcStart, IIf([EndDate]&gt;[Forms]![AskYear]![EOY],[Forms]![AskYear]![EOY],[EndDate]) AS CalcEnd, 1+DateDiff(&quot;m&quot;,[CalcStart],[CalcEnd]) AS Months<br>FROM Table1<br>WHERE (((Table1.StartDate) Between [Forms]![AskYear]![BOY] And [Forms]![AskYear]![EOY])) OR (((Table1.EndDate) Between [Forms]![AskYear]![BOY] And [Forms]![AskYear]![EOY]));<br><br>If either [StartDate] or [EndDate] in your table falls between BOY or EOY on the form, then that row should be included in the results.&nbsp;&nbsp;The number of months for a row can't be greater than 12.&nbsp;&nbsp;To calculate it, I use the iif function to set the beginning and end dates to be subtracted.&nbsp;&nbsp;If [StartDate] is earlier than [BOY], then I use [BOY] as my starting point.&nbsp;&nbsp;If [EndDate] is later than [EOY], then I use [EOY] as my ending point.&nbsp;&nbsp;I use the DateDiff function to calculate the number of months between the two dates and then I add one (this is because of the way you chose to enter data).<br><br>I assume you can do the rest of the calculation on your own.&nbsp;&nbsp;Hope this helps.

 

[Tmat]

I did as you explained and it works perfectly. But I am missing 12 months of usage for programs that start before and end after my specified year. I tried following your lead&nbsp;&nbsp;adding some more lines in the SQL statement but what ever I tried didn’t work. Is there a way to add if Startdate &lt; BOY and EndDate &gt;EOY then Months=12&nbsp;&nbsp;or should I try adding this to another field in the Query or make another query with just this criteria and bring it all together on a report?<br><br>Thanks so much for your help. <br>

 

[Tmat]

I did as you explained and it works perfectly. But I am missing 12 months of usage for programs that start before and end after my specified year. I tried following your lead&nbsp;&nbsp;adding some more lines in the SQL statement but what ever I tried didn’t work. Is there a way to add if Startdate &lt; BOY and EndDate &gt;EOY then Months=12&nbsp;&nbsp;or should I try adding this to another field in the Query or make another query with just this criteria and bring it all together on a report?<br><br>Thanks so much for your help

 

[rochelle]

Hi Tmat<br><br>Try changing the query to be: <br>SELECT IIf([StartDate]&lt;[Forms]![AskYear]![BOY],[Forms]![AskYear]![BOY],[StartDate]) AS CalcStart, IIf([EndDate]&gt;[Forms]![AskYear]![EOY],[Forms]![AskYear]![EOY],[EndDate]) AS CalcEnd, 1+DateDiff(&quot;m&quot;,[CalcStart],[CalcEnd]) AS Months<br>FROM Table1<br>WHERE ((([Forms]![AskYear]![ReportYear]) Between Year([startdate]) And Year([Enddate])));<br><br>The problem was the definition of which records to include.&nbsp;&nbsp;I neglected to consider the case you were having problems with.<br><br>I won't be able to get back to you again for several weeks because I'm off on vacation.&nbsp;&nbsp;&nbsp;&nbsp;Do let me know if this works.

 

[Tmat]

rochelle<br>It worked like a charm. Your good <br>Thanks for the help.