creating a quarterly report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I need help creating a quarterly report that is based on activation date
of a product. for example if my activation date is january my cost will be
cost *3 for the first quarter and so on. if my activation date is march then
my cost will be just that monthly cost for that quarter. I hope I'm clear
with my explanation.Thanks
 
Is your activation date field an actual "date" field per Access properties
definition or is it a textbox with the word of the month?
 
it is an actual date field

NetworkTrade said:
Is your activation date field an actual "date" field per Access properties
definition or is it a textbox with the word of the month?
Click to expand...
 
When the Report loads it can calculate the value you need.

In the property of the Report you can put this into the OnLoad event.
Copy/paste in the code below that is between the -------- and complete...( I
only demo months 1-3 here but it just repeats).

Also, of course replace your actual field names in the generic field names I
supply that are found between the [ ] ...

Note; since you say that the Qtrly cost is * 3 if January...then I am
presuming somewhere is to be found the single month cost field to use in the
multiplication. I arbitrarily named this field [MonthCostField]. Obviously
this must exist for the math to be done....

Harder Core VBers pride themselves on more efficient code - but I like it
simple even if it is longer...easier to read...just a stack of If/thens...

Private Sub Report_Load()
----------------------------------------------------

If DatePart("m", [ActivationDateField]) = 1 Then
[QuarterCostField] = ([MonthCostField] * 3)
End If

If DatePart("m", [ActivationDateField]) = 2 Then
[QuarterCostField] = ([MonthCostField] * 2)
End If

If DatePart("m", [ActivationDateField]) = 3 Then
[QuarterCostField] = ([MonthCostField] * 1)
End If

etc etc repeat for months 4-12...
 
Thank you very much NTC this was very helpful

NetworkTrade said:
When the Report loads it can calculate the value you need.

In the property of the Report you can put this into the OnLoad event.
Copy/paste in the code below that is between the -------- and complete...( I
only demo months 1-3 here but it just repeats).

Also, of course replace your actual field names in the generic field names I
supply that are found between the [ ] ...

Note; since you say that the Qtrly cost is * 3 if January...then I am
presuming somewhere is to be found the single month cost field to use in the
multiplication. I arbitrarily named this field [MonthCostField]. Obviously
this must exist for the math to be done....

Harder Core VBers pride themselves on more efficient code - but I like it
simple even if it is longer...easier to read...just a stack of If/thens...

Private Sub Report_Load()
----------------------------------------------------

If DatePart("m", [ActivationDateField]) = 1 Then
[QuarterCostField] = ([MonthCostField] * 3)
End If

If DatePart("m", [ActivationDateField]) = 2 Then
[QuarterCostField] = ([MonthCostField] * 2)
End If

If DatePart("m", [ActivationDateField]) = 3 Then
[QuarterCostField] = ([MonthCostField] * 1)
End If

etc etc repeat for months 4-12...
----------------------------
End Sub


hope this helps
--
NTC


2395 said:
it is an actual date field
Click to expand...
Click to expand...
 
Back
Top