Calculate penalties

[history]

Hello Brothers,,

I've a problem with calculating penalties

I've a calculation report contains :
ID - FullName - EntryDate - ResidenceExpireDate - Procedures - Penalty
*The Procedures are : IssuingResidence - RenewResidence - Exit

The penalty should be in some cases according to procedure :

IssuingResidence: Penalty = 100
if the period is more than 6 months since EntryDate

RenewResidence: Penalty = 100
if the period is more than 6 months since ResidenceExpireDate

Exit:
Case1: Penalty = 100
if the person hasn't Residence and the period is less than 6 months since EntryDate

Case2: Penalty = 100
if the person has Residence and the period is more than 6 months since ResidenceExpireDate

Case4enalty = 200
if the person hasn't Residence and the period is more than 6 months since EntryDate

Any help about that please ,,,

Thanks in advance

 

[history]

Hello Brothers,,,

I'm afraid that I couldn't explain well ,, but I'll add some details :

I've a problem with calculating penalties

I've a calculation report contains :
ID - FullName - EntryDate - ResidenceExpireDate - Procedures - ProcedureDate - Penalty
*The Procedures are : IssuingResidence - RenewResidence - Exit

The penalty should be in some cases according to procedure :

IssuingResidence: Penalty = 100
if ProcedureDate is more than 6 months since EntryDate

RenewResidence: Penalty = 100
if ProcedureDate is more than 6 months since ResidenceExpireDate

Exit:
Case1: Penalty = 100
if the person hasn't Residence and the period is less than 6 months since EntryDate

Case2: Penalty = 100
if ResidenceExpireDate Is Not Null and ProcedureDate is more than 6 months since ResidenceExpireDate

Case4enalty = 200
if ResidenceExpireDate Is Null and ProcedureDate is more than 6 months since EntryDate

waiting your help ,,,

Thanks in advance

 

[vbaInet]

*The Procedures are : IssuingResidence - RenewResidence - Exit

Are these the values in the Procedures field?

Exit:
Case1: Penalty = 100
if the person hasn't Residence and the period is less than 6 months since EntryDate

How do you check if a person hasn't got residence? Is this supposed to be:

if ResidenceExpireDate Is Null and the period is less than 6 months since EntryDate

??

 

[history]

Yes, these the values are in the Procedures field

If any one hasn't Residence : the field of ResidenceExpireDate will be empty

 

[vbaInet]

Here you go:

Public Function GetPenalty(entryDate, resExpireDate, procDate, proc As String) As Long
    If IsDate(procDate) = False Then
        Exit Function
    End If

    Select Case proc
        Case "IssuingResidence"
            If IsDate(entryDate) Then
                If DateDiff("d", DateAdd("m", 6, entryDate), procDate) > 0 Then
                    GetPenalty = 100
                End If
            End If
            
        Case "RenewResidence"
            If IsDate(resExpireDate) Then
                If DateDiff("d", DateAdd("m", 6, resExpireDate), procDate) > 0 Then
                    GetPenalty = 100
                End If
            End If
            
        Case "Exit"
            If IsNull(resExpireDate) And IsDate(entryDate) Then
                Dim dDiff As Long
                
                dDiff = DateDiff("d", DateAdd("m", 6, entryDate), procDate)
                
                If dDiff < 0 Then
                    GetPenalty = 100    ' Case 1
                ElseIf dDiff > 0 Then
                    GetPenalty = 200    ' Case 3
                End If
            End If
            
            ' Case 2
            If IsDate(resExpireDate) Then
                If DateDiff("d", DateAdd("m", 6, resExpireDate), procDate) > 0 Then
                    GetPenalty = 100
                End If
            End If
    End Select
End Function

Untested but it should work based on the logic you provided.

 

[history]

Thanks for your help

can you explain how to use it ???
I mean Getpenalty ,,,, is it Penalty or should I change ???

 

[vbaInet]

Put the code in a MODULE and in the query of the report, you put this:

Penalty: GetPenalty([EntryDate], [ResidenceExpireDate],  [ProcedureDate], [Procedures])

Are there some cases where Procedure is null?

 

[history]

Perfect it's working very nice

I'll work more on it and will tell you

Thanks Brother

 

[vbaInet]

No problemo!

Just one more question left to answer -->

Are there some cases where Procedure is null?

 

[history]

No Sir,

it shouldn't be because the report is for calculation of procedures, so every one has to has at least one procedure in the same date :
for example :
1- procedure "Medical Report" and procedure "Residence" in the same date
2- procedure "Renew Passport validity" in another date
3- procedure "Exit" ............... etc

 

[vbaInet]

That's fine then, because the code wasn't covering Nulls for Procedures