need function to build report? 2

[4281967]

I have a table (tblListing) that has:
Listing (a number representing an ad)
StartDate (date ad starts)
EndDate (date ad ends)
Cost (price or ad)

most ads run for 12 months (for example start may be something like 2/1/05 and end is 1/31/06)

what I need to do is list by month, the cost of each ad.

To make the math easy, let's just say all ads are 12 months and $1200 per year (which they arent)

Here is what my report needs to have:
(for this example Jan-Dec is 2006 and row 1 is for 4/1/05 to 3/31/06. Row 2 is 8/1/05 to 7/30/06)

AD#----JAN--FEB--MAR--APR--MAY--LUN--JUL--AUG--SEP--NOV--DEC
123----100--100--100
222----100--100--100--100--100--100--100

etc -
the "--" is just to hold the places (not needed on report)

anyway - any ideas on how I can do this?
I have several thousand Ads - and would like to get this into

 

[TheAceMan1]

How are ya 4281967 . . .
[ol][li]Is the data specific to the current year?[/li]
[li]What about year-end crossovers?[/li][/ol]

See Ya! . . . . . .

 

[PHV]

Provided that all ads run for full months, here a starting point (SQL code):
SELECT Listing As [AD#]
,Sum(IIf(StartDate<=#2006-01-31# AND EndDate>=#2006-01-01#,Cost/(1+DateDiff("m",StartDate,EndDate)),0)) As JAN
,Sum(IIf(StartDate<=#2006-02-28# AND EndDate>=#2006-02-01#,Cost/(1+DateDiff("m",StartDate,EndDate)),0)) As FEB
...
,Sum(IIf(StartDate<=#2006-12-31# AND EndDate>=#2006-12-01#,Cost/(1+DateDiff("m",StartDate,EndDate)),0)) As DEC
FROM tblListing
WHERE StartDate<=#2006-12-31# AND EndDate>=#2006-01-01#
GROUP BY Listing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[4281967]

Thanks for getting back to me so quick...

Aceman1, the data covers ads for 2005 and 2006. Several (most) of the 2005 ads do cross over into 2006. My report just needs to focus on Jan-Dec of 2006.

PHV, all ads run for full months - at lease one, at most 12... would your query work with year end cross overs, and would I run this for each ad?

 

[PHV]

Try it and let me know.

 

[CautionMP]

Here is another take on it. Use [tt]BuildReport()[/tt] below to create a new report. Then add the following code to the On Format event for the Detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intStart As Integer, intStop As Integer, intMonth As Integer
Dim strControl As String

'You can pass an open argument to the report to replace 2006 below
If Year(StartDate) < 2006 Then
  intStart = 1
Else
  intStart = Month(StartDate)
End If

'You can pass an open argument to the report to replace 2006 below
If Year(EndDate) < 2006 Then
  intStop = 12
Else
  intStop = Month(EndDate)
End If

'This will update the text boxes 1-12 to match your example
For intMonth = intStart To intStop
  strControl = "txt" & Format(intMonth, "00")
  Me.Controls(strControl) = Cost / DateDiff("m", StartDate, EndDate)
Next intMonth
End Sub

ANd here is the wizard code that will build the form for the above event. Please note that I used the table/field names from your original posting.

Public Sub BuildReport()
Dim rptNew As Access.Report
Dim NewControl As Access.Control
Set rptNew = CreateReport()
With rptNew
'  .Name = rptNew.Name
  .RecordSource = "SELECT * FROM tblListing;"
  'Start controls
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 1380, 60, 525, 225)
  NewControl.Name = "Label28"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 1980, 60, 525, 225)
  NewControl.Name = "Label29"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 2580, 60, 525, 225)
  NewControl.Name = "Label30"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 3180, 60, 525, 225)
  NewControl.Name = "Label31"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 3780, 60, 525, 225)
  NewControl.Name = "Label32"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 4380, 60, 525, 225)
  NewControl.Name = "Label33"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 4980, 60, 525, 225)
  NewControl.Name = "Label34"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 5580, 60, 525, 225)
  NewControl.Name = "Label35"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 6180, 60, 525, 225)
  NewControl.Name = "Label36"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 6780, 60, 525, 225)
  NewControl.Name = "Label37"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 7380, 60, 525, 225)
  NewControl.Name = "Label38"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 3, , , 7980, 60, 525, 225)
  NewControl.Name = "Label39"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , "Listing", 780, 60, 540, 240)
  NewControl.Name = "Listing"
  Set NewControl = CreateReportControl(rptNew.Name, 100, 0, , , 120, 60, 585, 225)
  NewControl.Name = "Label0"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 1380, 60, 480, 240)
  NewControl.Name = "txt01"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 1920, 60, 480, 240)
  NewControl.Name = "txt02"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 2520, 60, 480, 240)
  NewControl.Name = "txt03"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 3120, 60, 480, 240)
  NewControl.Name = "txt04"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 3720, 60, 480, 240)
  NewControl.Name = "txt05"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 4320, 60, 480, 240)
  NewControl.Name = "txt06"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 4920, 60, 480, 240)
  NewControl.Name = "txt07"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 5520, 60, 480, 240)
  NewControl.Name = "txt08"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 6120, 60, 480, 240)
  NewControl.Name = "txt09"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 6720, 60, 480, 240)
  NewControl.Name = "txt10"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 7320, 60, 480, 240)
  NewControl.Name = "txt11"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , , 7920, 60, 480, 240)
  NewControl.Name = "txt12"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , "StartDate", 8460, 60, 360, 240)
  NewControl.Name = "StartDate"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , "EndDate", 8820, 60, 360, 240)
  NewControl.Name = "EndDate"
  Set NewControl = CreateReportControl(rptNew.Name, 109, 0, , "Cost", 9180, 60, 360, 240)
  NewControl.Name = "Cost"
End With
End Sub

Hope this helps,
CMP

Instant programmer, just add coffee.

 

[CautionMP]

P.S. I forgot to add the captions on the labels and the number formats on the [tt]txt01-12[/tt], but you should get the idea.

CMP

Instant programmer, just add coffee.

 

[PHV]

I'd replace this:
If Year(EndDate) < 2006 Then
with this:
If Year(EndDate) > 2006 Then

 

[4281967]

PHV -
this worked exactly as I needed, except for 1 thing - the monthly vales are = cost/11 instead of cost/12

I tried to change the 1+ in you SQL to 2+ which worked for some, but then the others are now cost/13
Although the dollar amounts are off slightly, they are populating the months correctly - Can you help me figure out the correct month cost on these?

Thanks


CautionMP - thanks - I am trying to implement your solution now

 

[PHV]

Which records exhibit a monthly value of Cost/11 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[4281967]

it looks like any ad that has a contract of less than 6 months

 

[PHV]

Please, post some sample values of:
StartDate, EndDate, Cost, retrieved value, expected value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[4281967]

here's the first two I came across
StartDate EndDate Cost retrieved expected
3/1/2005 1/31/2006 $20,835.36 1894 1736
7/1/2005 5/31/2006 $25,937.73 2358 2161

 

[PHV]

Can you please explain why a cost of $20,835.36 for 11 month should be considered as $1,736 monthly ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[4281967]

PHV - I am sorry - your right.. I am not sure what I was thinking, but you SQL works perfect. Wish I could give you a second star but this won't let me (i tried) - you saved me a ton of time.
Thanks again