Most suitable function to calculate laydays (1 Viewer)

Leo_Polla_Psemata · Mar 25, 2014

Hi
Suppose that you want to make a formula which will calculate time periods based on a given tariff.

To be more precise.

Suppose you can "get in" somewhere, entrance is $2.
You are free to stay there, without additional charge, for 5 days.
Then, for the next 5 days, the charge is $1 per day
The next 7 days, the charge is $1.5 per day
Thereafter, $3 per day.

In this scenario, we can make a query, we use datediff function to see the number of days between "gate in" and "gate out".
The problem is that the tariff is not very simple and the query gets to big and difficult to control with lot's of "iif" and may be I need to make more than 4 queries and union select and so on.

I tried to find in the docmd if there is any suitable function that I could use.
If anyone can tell, it would be of great help .

Thanks

plog · Mar 25, 2014

There's no custom function. You would have to write your own.

TJPoorman · Mar 25, 2014

You would need to build a public function then call that function from your query. Something like this should work for what you're looking for.

Code:

Public Function ChargeAmount(datIn As Date, datOut As Date) As Double
Dim lngNumDays As Long

lngNumDays = DateDiff("d", datIn, datOut)
Select Case lngNumDays
    Case Is <= 5: ChargeAmount = 2  'First five days are 2
    Case Is <= 10: ChargeAmount = 2 + ((lngNumDays - 5) * 1)    'Next five days are 1
    Case Is <= 17: ChargeAmount = 7 + ((lngNumDays - 10) * 1.5) 'Next seven days are 1.5
    Case Else: ChargeAmount = 17.5 + ((lngNumDays - 17) * 3)    'Remaining days are 3
End Select
End Function

Leo_Polla_Psemata · Mar 25, 2014

Thanks plog and Tjpoorman

Tjpoorman
I will work like your suggestion
Just because I am not familiar with "codes"

Some units which have "gate in" are still idle "gate in" , no "gate out" and therefore
I have to calculate up to today and notify that up to today charges are that much,
how can I nest the IIF "gate out" is null then consider charges up to now() ? in the above

Once I resolve this I will ask you to help on the next step which is
different tariff because of different unit types (large units, small units, medium units ... )

namliam · Mar 25, 2014

Just call TJP's function as:
Chargeamount(GateIn, NZ(GateOut,Date())

For the unit sizes just add the unit type to the function call and use a DLookup to get the rate...

Leo_Polla_Psemata · Mar 26, 2014

TJPoorman and Namliam
Many many thanks for your assistance

I am typing my first vba.
I don't know anything, just try to copy and make it look like other examples
i see here and there

Now I copy paste the first suggestion of TJPoorman and with some minor changes I made it .
I tried to make another adding the tariff code but there is an error

Compile error , select case without End select

Here you are both codes, the first works, the second no

Option Compare Database

Public Function ChargeAmount(datIn As Date, datOut As Date) As Double
Dim lngNumDays As Long

lngNumDays = DateDiff("d", datIn, datOut)
Select Case lngNumDays
Case Is <= 10: ChargeAmount = 0 'First 10 days are 0
Case Is <= 15: ChargeAmount = 0 + ((lngNumDays - 5) * 10) 'Next five days are 10
Case Is <= 20: ChargeAmount = 50 + ((lngNumDays - 15) * 20) 'Next five days are 20
Case Else: ChargeAmount = 150 + ((lngNumDays - 20) * 30) 'Remaining days are 30
End Select
End Function

Public Function DChargeAmount(tariff As Variant, datIn As Date, datOut As Date) As Double

lngNumDays = DateDiff("d", datIn, datOut)
Select Case tariff
Case Is = 1
Select Case lngNumDays
Case Is <= 10: DChargeAmount = 0 'First 10 days free
Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 10) 'Next four days are 10 per day
Case Is <= 19: DChargeAmount = 40 + ((lngNumDays - 14) * 12.5) 'Next five days are 12.5 per day
Case Else: DChargeAmount = 102.5 + ((lngNumDays - 19) * 25) 'Remaining days are 25 per day
Select Case tariff
Case Is = 2
Select Case lngNumDays
Case Is <= 10: DChargeAmount = 0 'First 10 days free
Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 20) 'Next four days are 20 per day
Case Is <= 19: DChargeAmount = 80 + ((lngNumDays - 14) * 25) 'Next five days are 25 per day
Case Else: DChargeAmount = 205 + ((lngNumDays - 19) * 50) 'Remaining days are 50 per day
Select Case tariff
Case Is = 3
Select Case lngNumDays
Case Is <= 10: DChargeAmount = 0 'First 10 days free
Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 40) 'Next four days are 40 per day
Case Is <= 19: DChargeAmount = 160 + ((lngNumDays - 14) * 80) 'Next five days are 80 per day
Case Else: DChargeAmount = 560 + ((lngNumDays - 19) * 80) 'Remaining days are 80 per day
Select Case tariff
Case Else: DChargeAmount = 9999
End Select

End Function

AOB · Mar 26, 2014

Recommend you use indentation to keep track of your Select blocks and make sure you end each one appropriately :

Code:

Public Function DChargeAmount(tariff As Variant, datIn As Date, datOut As Date) As Double
 
lngNumDays = DateDiff("d", datIn, datOut)
 
[COLOR=blue]Select Case tariff[/COLOR]
[INDENT][COLOR=blue]Case Is = 1[/COLOR]
[/INDENT][INDENT][INDENT][COLOR=teal]Select Case lngNumDays[/COLOR]
[/INDENT][/INDENT][INDENT][INDENT][INDENT][COLOR=teal]Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 10)[/COLOR]
[COLOR=teal]Case Is <= 19: DChargeAmount = 40 + ((lngNumDays - 14) * 12.5)[/COLOR]
[COLOR=teal]Case Else: DChargeAmount = 102.5 + ((lngNumDays - 19) * 25)[/COLOR]
 
[/INDENT][COLOR=teal]End Select[/COLOR]
 
[/INDENT][COLOR=blue]Case Is = 2[/COLOR]
[COLOR=#0000ff][/COLOR] 
[INDENT][COLOR=teal]Select Case lngNumDays[/COLOR]
[COLOR=#008080][/COLOR] 
[INDENT][COLOR=teal]Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 20)[/COLOR]
[COLOR=teal]Case Is <= 19: DChargeAmount = 80 + ((lngNumDays - 14) * 25)[/COLOR]
[COLOR=teal]Case Else: DChargeAmount = 205 + ((lngNumDays - 19) * 50)[/COLOR]
 
[/INDENT][COLOR=teal]End Select[/COLOR]
 
[/INDENT][COLOR=blue]Case Is = 3[/COLOR]
 
[INDENT][COLOR=teal]Select Case lngNumDays[/COLOR]
 
[INDENT][COLOR=teal]Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 40)[/COLOR]
[COLOR=teal]Case Is <= 19: DChargeAmount = 160 + ((lngNumDays - 14) * 80)[/COLOR]
[COLOR=teal]Case Else: DChargeAmount = 560 + ((lngNumDays - 19) * 80)[/COLOR]
 
[/INDENT][COLOR=teal]End Select[/COLOR]
 
[/INDENT][COLOR=blue]Case Else: DChargeAmount = 9999[/COLOR]
 
[/INDENT][COLOR=blue]End Select[/COLOR]
 
End Function

Leo_Polla_Psemata · Mar 26, 2014

It is unbelievable.
Thanks you all.... I made it .... I can't believe it , I just can't.

I started back in 1999 with excel.
At that time just very few in the company knew about I.T., everything, or almost everything was performed manually.
I use to calculate manually , in a piece of paper all these calculations and then transfer to a word and fax to clients.
Then I jump to excel which was better than the pen but not the proper tool.
Although calculations were executed faster, i still used to have to copy in a word or another excel spreadsheet and fax.
Then I just came across to Access.
After some years I could work well with forms tables reports queries and perform
tasks easily and fast. No one in the company knew how come I can print the reports so fast.
The happy years finished one day when we were informed that the company is acquired by another bigger company and we had to lose our job, you know how it works.
I stay some months unemployed , I followed some ACCESS courses, I improve my skills
but the VBA was ALWAYS a mystery, something very dark and complicated.
I bought some books with examples and ...nothing, I just couldn't crack it.

I found a new job, a small company that fits perfectly to ACCESS. You know what I mean.
Few months ago, I have had some experience with docmd , i managed to create events
and today I just understood that VBA is not parallel universe. Long way to go of course.

Thanks anyway

THANKS THANKS THANKS THANKS THANKS THANKS THANKS THANKS THANKS

Leo_Polla_Psemata · Sep 24, 2015

AOB said:

Recommend you use indentation to keep track of your Select blocks and make sure you end each one appropriately :

Code:

Public Function DChargeAmount(tariff As Variant, datIn As Date, datOut As Date) As Double
 
lngNumDays = DateDiff("d", datIn, datOut)
 
[COLOR=blue]Select Case tariff[/COLOR][INDENT][COLOR=blue]Case Is = 1[/COLOR]
[/INDENT][INDENT][INDENT][COLOR=teal]Select Case lngNumDays[/COLOR]
[/INDENT][/INDENT][INDENT][INDENT][INDENT][COLOR=teal]Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 10)[/COLOR]
[COLOR=teal]Case Is <= 19: DChargeAmount = 40 + ((lngNumDays - 14) * 12.5)[/COLOR]
[COLOR=teal]Case Else: DChargeAmount = 102.5 + ((lngNumDays - 19) * 25)[/COLOR]
 
[/INDENT][COLOR=teal]End Select[/COLOR]
 
[/INDENT][COLOR=blue]Case Is = 2[/COLOR]
 [INDENT][COLOR=teal]Select Case lngNumDays[/COLOR]
 [INDENT][COLOR=teal]Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 20)[/COLOR]
[COLOR=teal]Case Is <= 19: DChargeAmount = 80 + ((lngNumDays - 14) * 25)[/COLOR]
[COLOR=teal]Case Else: DChargeAmount = 205 + ((lngNumDays - 19) * 50)[/COLOR]
 
[/INDENT][COLOR=teal]End Select[/COLOR]
 
[/INDENT][COLOR=blue]Case Is = 3[/COLOR]
 [INDENT][COLOR=teal]Select Case lngNumDays[/COLOR]
 [INDENT][COLOR=teal]Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 40)[/COLOR]
[COLOR=teal]Case Is <= 19: DChargeAmount = 160 + ((lngNumDays - 14) * 80)[/COLOR]
[COLOR=teal]Case Else: DChargeAmount = 560 + ((lngNumDays - 19) * 80)[/COLOR]
 
[/INDENT][COLOR=teal]End Select[/COLOR]
 
[/INDENT][COLOR=blue]Case Else: DChargeAmount = 9999[/COLOR]
 
[/INDENT][COLOR=blue]End Select[/COLOR]
 
End Function

Hi
In this code, which I use since last year very very successfully and I have add quality in my job, I need to use one more "select case".
The tariff has changed.
So for every "datin" before 20 September the current code is correct
For every "datin" after 21st September, I will use the same code with new tariff, how can I make it ?
The code "budy" must not change, only add one more select case.

AOB · Sep 24, 2015

See in red...

Code:

Public Function DChargeAmount(tariff As Variant, datIn As Date, datOut As Date) As Double
 
    lngNumDays = DateDiff("d", datIn, datOut)
 
[COLOR=red]   If datIn < DateSerial(2015, 9, 20) Then[/COLOR]
 
[COLOR=blue]       Select Case tariff[/COLOR]
 
[COLOR=blue]           Case Is = 1[/COLOR]
 
[COLOR=teal]               Select Case lngNumDays[/COLOR]
 
[COLOR=teal]                   Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]                   Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 10)[/COLOR]
[COLOR=teal]                   Case Is <= 19: DChargeAmount = 40 + ((lngNumDays - 14) * 12.5)[/COLOR]
[COLOR=teal]                   Case Else: DChargeAmount = 102.5 + ((lngNumDays - 19) * 25)[/COLOR]
 
[COLOR=teal]               End Select[/COLOR]
 
[COLOR=blue]           Case Is = 2[/COLOR]
 
[COLOR=teal]               Select Case lngNumDays[/COLOR]
 
[COLOR=teal]                   Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]                   Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 20)[/COLOR]
[COLOR=teal]                   Case Is <= 19: DChargeAmount = 80 + ((lngNumDays - 14) * 25)[/COLOR]
[COLOR=teal]                   Case Else: DChargeAmount = 205 + ((lngNumDays - 19) * 50)[/COLOR]
 
[COLOR=teal]               End Select[/COLOR]
 
[COLOR=blue]           Case Is = 3[/COLOR]
 
[COLOR=teal]               Select Case lngNumDays[/COLOR]
 
[COLOR=teal]                   Case Is <= 10: DChargeAmount = 0[/COLOR]
[COLOR=teal]                   Case Is <= 14: DChargeAmount = ((lngNumDays - 10) * 40)[/COLOR]
[COLOR=teal]                   Case Is <= 19: DChargeAmount = 160 + ((lngNumDays - 14) * 80)[/COLOR]
[COLOR=teal]                   Case Else: DChargeAmount = 560 + ((lngNumDays - 19) * 80)[/COLOR]
 
[COLOR=teal]               End Select[/COLOR]
 
[COLOR=blue]           Case Else: DChargeAmount = 9999[/COLOR]
 
[COLOR=blue]       End Select[/COLOR]
 
[COLOR=red]   Else[/COLOR]
 
[COLOR=red]       ' Insert your modified logic for post-Sept 20th here[/COLOR]
 
[COLOR=red]   End If[/COLOR]
 
End Function

Or is this seasonal? (i.e. pre-Sept every year it's one saet of rules, and post-Sept every year it's the other?)

Also :

So for every "datin" before 20 September the current code is correct
For every "datin" after 21st September, I will use the same code with new tariff

What happens on September 20th and 21st?

Leo_Polla_Psemata · Sep 24, 2015

Hi
It returns an error
It says
Compile error
Else without if

No , it is not seasonal.
The "laydays" tariff has been changed, now they apply less free days

AOB · Sep 24, 2015

Compiles fine for me? Can you paste your exact code?

Leo_Polla_Psemata · Sep 24, 2015

here you are,, (i made some changes trying to remove compile error)

Code:

Public Function PortStor(sztp As Variant, DatIn As Date, datOut As Date) As Double
Dim lngNumDays As Long

lngNumDays = DateDiff("d", DatIn, datOut) + 1
    
    If DatIn < DateSerial(2015, 9, 20) Then
    
Select Case sztp
Case "DV20", "OT20", "FL20", "RH20", "TK20"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = (lngNumDays - 10) * 3    'Next five days are 3
        Case Is <= 20: PortStor = 15 + ((lngNumDays - 15) * 5) 'Next five days are 5
        Case Else: PortStor = 40 + ((lngNumDays - 20) * 7)    'Remaining days are 30
    End Select

Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
        Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 10) 'Next five days are 5
        Case Else: PortStor = 80 + ((lngNumDays - 20) * 14)    'Remaining days are 14
    End Select
    
    ElseIf DatIn >= DateSerial(2015, 9, 20) Then
    
    Select Case sztp
Case "DV20", "OT20", "FL20", "RH20", "TK20"

    Select Case lngNumDays
        Case Is <= 5: PortStor = 0   'First 5 days are 0
        Case Is <= 15: PortStor = (lngNumDays - 5) * 3    'Next ten days are 3
        Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 5) 'Next five days are 5
        Case Else: PortStor = 55 + ((lngNumDays - 20) * 7)    'Remaining days are 30
    End Select

Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
        Case Is <= 20: PortStor = 60 + ((lngNumDays - 15) * 10) 'Next five days are 5
        Case Else: PortStor = 110 + ((lngNumDays - 20) * 14)    'Remaining days are 14
    End Select
    
Else: PortStor = 999
    
    End If
    
End Function

AOB · Sep 24, 2015

You can't mix your blocks up like that. Each one must be able to 'stand alone'. Either put the Select Case / End Select wholly inside the If / End If block, or vice versa.

Code:

Select Case x
    Case y
        If z Then
            ' Course of action
        Else
            ' Alternate course of action
        End If
End Select

or

Code:

If x Then
    Select Case y
        Case z
            ' Course of action
    End Select
Else
    ' Alternate course of action
End If

You have the If statement outside the Select Case block, but then the ElseIf statement inside it - you can't do that.

From a purely syntax perspective*, it should be :

Code:

Public Function PortStor(sztp As Variant, DatIn As Date, datOut As Date) As Double
 
    Dim lngNumDays As Long
 
    lngNumDays = DateDiff("d", DatIn, datOut) + 1
 
    Select Case sztp
 
        Case "DV20", "OT20", "FL20", "RH20", "TK20"
 
            Select Case lngNumDays
                Case Is <= 10: PortStor = 0  'First 10 days are 0
                Case Is <= 15: PortStor = (lngNumDays - 10) * 3    'Next five days are 3
                Case Is <= 20: PortStor = 15 + ((lngNumDays - 15) * 5) 'Next five days are 5
                Case Else: PortStor = 40 + ((lngNumDays - 20) * 7)    'Remaining days are 30
            End Select
 
        [COLOR=blue]Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"[/COLOR]
 
            If DatIn < DateSerial(2015, 9, 20) Then
 
                Select Case lngNumDays
                    Case Is <= 10: PortStor = 0  'First 10 days are 0
                    Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
                    Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 10) 'Next five days are 5
                    Case Else: PortStor = 80 + ((lngNumDays - 20) * 14)    'Remaining days are 14
                End Select
 
            Else
 
                Select Case sztp
 
                    [COLOR=red]Case "DV20", "OT20", "FL20", "RH20", "TK20"[/COLOR]
 
                        Select Case lngNumDays
                            Case Is <= 5: PortStor = 0   'First 5 days are 0
                            Case Is <= 15: PortStor = (lngNumDays - 5) * 3    'Next ten days are 3
                            Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 5) 'Next five days are 5
                            Case Else: PortStor = 55 + ((lngNumDays - 20) * 7)    'Remaining days are 30
                        End Select
 
                End Select
 
            End If
 
        [COLOR=darkgreen]Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"[/COLOR]
 
            Select Case lngNumDays
                Case Is <= 10: PortStor = 0  'First 10 days are 0
                Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
                Case Is <= 20: PortStor = 60 + ((lngNumDays - 15) * 10) 'Next five days are 5
                Case Else: PortStor = 110 + ((lngNumDays - 20) * 14)    'Remaining days are 14
            End Select
 
    End Select
 
End Function

*However, this is never going to work

- look at the two lines highlighted in blue and red?

Reading the line in blue, the contents of the block that follows will only be triggered if sztp is one of "DV40", "OT40", "FL40", "RH40", "TK40" or "HC40"

But then within that block, you check sztp again and choose an action based on whether sztp is one of "DV20", "OT20", "FL20", "RH20", "TK20"

The code couldn't possibly have reached this point if sztp held one of the values in red (because of the criteria used for the line in blue) Therefore this particular case can never be achieved??

The line in green also doesn't make any sense - it's exactly the same conditions as for the previous case (in blue)? So the code can never reach this point either (if it satisfies this condition, it will adopt the logic in the previous block and move on)

You need to re-examine your conditions and re-write your sequence of blocks because it doesn't make sense as it stands?

Leo_Polla_Psemata · Sep 24, 2015

Okay, please have a look

This function works just fine , I call A1

Code:

Public Function PortStor(SZTP As Variant, DatIn As Date, datOut As Date) As Double
Dim lngNumDays As Long

lngNumDays = DateDiff("d", DatIn, datOut) + 1
    
Select Case SZTP

Case "DV20", "OT20", "FL20", "RH20", "TK20"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = (lngNumDays - 10) * 3    'Next five days are 3
        Case Is <= 20: PortStor = 15 + ((lngNumDays - 15) * 5) 'Next five days are 5
        Case Else: PortStor = 40 + ((lngNumDays - 20) * 7)    'Remaining days are 30
    End Select

Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
        Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 10) 'Next five days are 5
        Case Else: PortStor = 80 + ((lngNumDays - 20) * 14)    'Remaining days are 14
    End Select
    
Case Else: PortStor = 999

End Select

End Function

After 20 September the code should calculate as this one I call A2

Code:

Public Function PortStor(SZTP As Variant, DatIn As Date, datOut As Date) As Double
Dim lngNumDays As Long

lngNumDays = DateDiff("d", DatIn, datOut) + 1
    
Select Case SZTP

Case "DV20", "OT20", "FL20", "RH20", "TK20"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = (lngNumDays - 10) * 3    'Next five days are 3
        Case Is <= 20: PortStor = 15 + ((lngNumDays - 15) * 5) 'Next five days are 5
        Case Else: PortStor = 40 + ((lngNumDays - 20) * 7)    'Remaining days are 30
    End Select

Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"

    Select Case lngNumDays
        Case Is <= 10: PortStor = 0  'First 10 days are 0
        Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
        Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 10) 'Next five days are 5
        Case Else: PortStor = 80 + ((lngNumDays - 20) * 14)    'Remaining days are 14
    End Select
    
Case Else: PortStor = 999

End Select

End Function

The database stores data that are datIn before or after 20Sep.

How can I combine these two codes based on the DatIn ?

Suppose this should work

If (date is < to 20/9) Then
A1
Else
A2
End If

AOB · Sep 24, 2015

I don't see any difference between A1 and A2?...

Leo_Polla_Psemata · Nov 15, 2015

Hi
I have insert iif function for days before and after 19 Sep , tari = IIf([DatIn] < #9/17/2015#, 1, 2)
The code is now as follow and works.
I don't know if there are any errors in the structure, for example
Three "end select" at the bottom ?
Could someone inspect it and advice if there is some mistake?

Code:

Public Function PortStor(SZTP As Variant, DatIn As Date, datOut As Date) As Double
Dim lngNumDays As Long

lngNumDays = DateDiff("d", DatIn, datOut) + 1
tari = IIf([DatIn] < #9/17/2015#, 1, 2)


    Select Case tari
        Case 1
    
                    Select Case SZTP
                    Case "DV20", "OT20", "FL20", "RH20", "TK20"
                    
                        Select Case lngNumDays
                            Case Is <= 10: PortStor = 0  'First 10 days are 0
                            Case Is <= 15: PortStor = (lngNumDays - 10) * 3    'Next five days are 3
                            Case Is <= 20: PortStor = 15 + ((lngNumDays - 15) * 5) 'Next five days are 5
                            Case Else: PortStor = 40 + ((lngNumDays - 20) * 7)    'Remaining days are 7
                        End Select
                    
                    Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"
                    
                        Select Case lngNumDays
                            Case Is <= 10: PortStor = 0  'First 10 days are 0
                            Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
                            Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 10) 'Next five days are 10
                            Case Else: PortStor = 80 + ((lngNumDays - 20) * 14)    'Remaining days are 14
                        End Select
     End Select
          
        Case 2
      
                        Select Case SZTP
                        Case "DV20", "OT20", "FL20", "RH20", "TK20"
                    
                        Select Case lngNumDays
                            Case Is <= 5: PortStor = 0   'First 5 days are 0
                            Case Is <= 15: PortStor = (lngNumDays - 5) * 3    'Next ten days are 3
                            Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 5) 'Next five days are 5
                            Case Else: PortStor = 55 + ((lngNumDays - 20) * 7)    'Remaining days are 7
                        End Select
                    
                    Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"
                    
                        Select Case lngNumDays
                            Case Is <= 5: PortStor = 0  'First 10 days are 0
                            Case Is <= 15: PortStor = ((lngNumDays - 5) * 6)    'Next five days are 6
                            Case Is <= 20: PortStor = 60 + ((lngNumDays - 15) * 10) 'Next five days are 10
                            Case Else: PortStor = 110 + ((lngNumDays - 20) * 14)    'Remaining days are 14
                        End Select

End Select
End Select
End Function

namliam · Nov 16, 2015

Why would you think there is any mistake? Though I wouldn't use an IIF in a function personally.

AOB · Nov 16, 2015

If it works, chances are it's fine? Not sure what your concern is around the 3 x End Select's - that's usually what happens when you have three blocks embedded within one another. Eventually you have to close out the blocks; there's nothing unusual in several blocks ending in quick succession.

If I were to offer one piece of advice it would be this; use indentation to your advantage, especially with cascading blocks such as these, it makes it so much easier for you to follow and debug. It's not just about aesthetic - it's about being able to track exactly what's going on with your code (e.g. your "3 x End Selects" question/concern - proper indentation helps considerably!)

Congratulations on getting the code to work for you!

Code:

Public Function PortStor(SZTP As Variant, DatIn As Date, datOut As Date) As Double
  
     Dim lngNumDays As Long
  
     lngNumDays = DateDiff("d", DatIn, datOut) + 1
  
     tari = IIf([DatIn] < #9/17/2015#, 1, 2)
  
     Select Case tari
  
         Case 1
  
             Select Case SZTP
  
                 Case "DV20", "OT20", "FL20", "RH20", "TK20"
  
                     Select Case lngNumDays
  
                         Case Is <= 10: PortStor = 0  'First 10 days are 0
                         Case Is <= 15: PortStor = (lngNumDays - 10) * 3    'Next five days are 3
                         Case Is <= 20: PortStor = 15 + ((lngNumDays - 15) * 5) 'Next five days are 5
                         Case Else: PortStor = 40 + ((lngNumDays - 20) * 7)    'Remaining days are 7
  
                     End Select
  
                 Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"
  
                     Select Case lngNumDays
  
                         Case Is <= 10: PortStor = 0  'First 10 days are 0
                         Case Is <= 15: PortStor = ((lngNumDays - 10) * 6)    'Next five days are 6
                         Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 10) 'Next five days are 10
                         Case Else: PortStor = 80 + ((lngNumDays - 20) * 14)    'Remaining days are 14
  
                     End Select
  
             End Select
  
         Case 2
  
             Select Case SZTP
  
                 Case "DV20", "OT20", "FL20", "RH20", "TK20"
  
                     Select Case lngNumDays
  
                         Case Is <= 5: PortStor = 0   'First 5 days are 0
                         Case Is <= 15: PortStor = (lngNumDays - 5) * 3    'Next ten days are 3
                         Case Is <= 20: PortStor = 30 + ((lngNumDays - 15) * 5) 'Next five days are 5
                         Case Else: PortStor = 55 + ((lngNumDays - 20) * 7)    'Remaining days are 7
  
                     End Select
  
                 Case "DV40", "OT40", "FL40", "RH40", "TK40", "HC40"
  
                     Select Case lngNumDays
  
                         Case Is <= 5: PortStor = 0  'First 10 days are 0
                         Case Is <= 15: PortStor = ((lngNumDays - 5) * 6)    'Next five days are 6
                         Case Is <= 20: PortStor = 60 + ((lngNumDays - 15) * 10) 'Next five days are 10
                         Case Else: PortStor = 110 + ((lngNumDays - 20) * 14)    'Remaining days are 14
  
                     End Select
  
             End Select
  
     End Select
  
 End Function

Most suitable function to calculate laydays (1 Viewer)

Registered User.

Banishment Pending

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

Similar threads

Users who are viewing this thread