Report based on Start Date

[Tarbuza]

I have following field in my report

Action
Start Data
End Date
Qty
2003 : Formula is Qty*5, if start date falls in 2003 then Qty*6
2004 : Formula is Qty*12, if start date falls in 2004 then Qty*13
2005 : Formulat is Qty*12, if start date falls in 2005 then Qty*13

I started something like this but I am stuck as I am new to Access VBA. Please help.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not IsNull(Me. START_DT) Then

Select Case Year(Me. START_DT)
Case 2002

Case 2004

Case 2005

Case 2006

End Select
End If
End Sub

 

[MIKElaw]

Try The "Year" function. It's there in the VB help system

Year(Date) will return the year of the Date passed

11/7/2003 with return 2003 etc..

Case Year(Date) = 2002
Etc.

Hope this helps

Mike Law

 

[Tarbuza]

Thanks for your reply. I understand that part.

How can I store value inside a name called txt2004? I tried to do the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Not IsNull(Me. START_DT) Then

Select Case Year(Me. START_DT)
Case 2002

Case 2004
'Here is a problem. I am getting error message.
Me.txt2004=Me.txt2004*12
Case 2005

Case 2006

End Select
End If
End Sub