Library Fine Qurey

[nj_best]

Issue date is 15-jan-15 and return Date is 25-jan-15
total days are 10 day.
Now first Five days will be charge normal as $5 but upper 5 days these are over days because book can be hold for five days. so next 5 days will be charged as $7 per day as a fine.
Plz Give me Query about this

 

[CJ_London]

can't give you a query since you haven't provided details about your table(s) but the calculation will be

iif(datediff("d",issuedate,returndate)<5,datediff("d",issuedate,returndate)*$5,5*$5)+iif(datediff("d",issuedate,returndate)>5,(datediff("d",issuedate,returndate)-5)*$7,0)

 

[pbaldy]

Both moderated posts here approved, duplicate thread deleted.

 

[vbaInet]

A function is in order:

Public Function OverdueFine(DueDate As Date, _
                            InitialDays As Integer, _
                            InitialFine As Integer, _
                            LaterFine As Integer) As Long
    Dim lngOverdue As Long
    
    lngOverdue = DateDiff("d", DueDate, Date)
    
    If lngOverdue > InitialFine Then
        OverdueFine = (InitialFine ^ 2) + ((lngOverdue - InitialDays) * LaterFine)
    ElseIf lngOverdue > 0 Then
        OverdueFine = lngOverdue * InitialFine
    End If
End Function

E.g.

=OverdueFine(#6/25/2015#, 5, 5, 7)

 

[nj_best]

can't give you a query since you haven't provided details about your table(s) but the calculation will be

iif(datediff("d",issuedate,returndate)<5,datediff("d",issuedate,returndate)*$5,5*$5)+iif(datediff("d",issuedate,returndate)>5,(datediff("d",issuedate,returndate)-5)*$7,0)

Thanks. I will Try it soon and tell you what will be the result.
Thanks a lot. God Bless You

 

[nj_best]

Thanks CJ_LONDON
GOd Bless YOu

 

[nj_best]

ThankS a Lot CJ_LOndon

 

[nj_best]

can't give you a query since you haven't provided details about your table(s) but the calculation will be

iif(datediff("d",issuedate,returndate)<5,datediff("d",issuedate,returndate)*$5,5*$5)+iif(datediff("d",issuedate,returndate)>5,(datediff("d",issuedate,returndate)-5)*$7,0)

Thanks for help me God Bless You

 

[nj_best]

my table is issuedate returndate status

 

[vbaInet]

Spotted a typo. Change this

    If lngOverdue > [COLOR="Red"]InitialFine[/COLOR] Then

to this:

    If lngOverdue > [COLOR="Blue"]InitialDays[/COLOR] Then

 

[pbaldy]

Approved nj's replies, which for some reason were moderated.

 

[vbaInet]

Strange that it keeps getting moderated.

 

[nj_best]

Re: Library Fine Qurey MDB request

A function is in order:

Public Function OverdueFine(DueDate As Date, _
                            InitialDays As Integer, _
                            InitialFine As Integer, _
                            LaterFine As Integer) As Long
    Dim lngOverdue As Long
    
    lngOverdue = DateDiff("d", DueDate, Date)
    
    If lngOverdue > InitialFine Then
        OverdueFine = (InitialFine ^ 2) + ((lngOverdue - InitialDays) * LaterFine)
    ElseIf lngOverdue > 0 Then
        OverdueFine = lngOverdue * InitialFine
    End If
End Function

E.g.

=OverdueFine(#6/25/2015#, 5, 5, 7)

Sir,
Thanks for respons.
Butt tell me where this code will be insert. in form design view or query design view. And my field are not according you code. if i want to apply so will have to make field according your vba code myself.
So plz give me database file if you have related to libray inventory system.
Or give me the idea or fields according to your vba code.
Thanks

 

[vbaInet]

Ok, I wrote that just to give you an idea, but I've now changed it to suit. Have a look at the comments as well.

Public Function RentalCharge(IssueDate As Date, _
                             ReturnDate As Variant, _
                             InitialDays As Integer, _
                             InitialCharge As Integer, _
                             LateFine As Integer) As Long
    ' @IssueDate: Date book was issued, e.g. 15-Jan-15
    ' @ReturnDate: Date book was returned, e.g. 25-Jan-15
    ' @InitialDays: Number of days to charge normal rate, e.g. 5 days
    ' @InitialCharge: Normal daily charge, e.g. $5
    ' @LateFine: Late fine, e.g. $7
    
    Dim lngDays As Long
    
    If IsNull(ReturnDate) Then Exit Function
    
    lngDays = DateDiff("d", IssueDate, ReturnDate)
    
    If lngDays > InitialDays Then
        RentalCharge = (InitialCharge ^ 2) + ((lngDays - InitialDays) * LateFine)
    ElseIf lngDays > 0 Then
        RentalCharge = lngDays * InitialCharge
    End If
End Function

Copy and paste it into a new Module. To use it in the query, paste the following in a new Column in the query:

RentalFee: RentalCharge([IssueDate], [ReturnDate], 5, 5, 7)

Using a function offers more flexibility and it will perform better.

 

[nj_best]

Thanks vbaInet/
your code is working.
but one thing which is that you wrote a long code for module and query for calculate the fine charges and normal charges but CJ_LONDON give me jus a simple query its also working perfect. your procedure have you step one a long vba code second a query.
But CJ_LONDON have just a query and working done.
Are there is difference in your both mothed.
Plz tell me .
Otherwise a lot of thanks for give me these solution. thnaks.

 

[vbaInet]

Both methods are fine and are similar. The only difference with using a function is that you don't need to change the implementation, all you do is pass the relevant parameters to it. For example, if the rate changes in the future you pass the new parameters to the function.

The other thing is that a function will perform better in this case because the IIF() function in the expression provided by CJ evaluates all parts whereas the IF statement in the function will only evaluate one part hence making it quicker.

I hope it makes sense.

 

[CJ_London]

hence making it quicker

Not sure I agree with you there - In my experience (with regards Access) calling UDF's from a query is always slower than working with the data directly in the query - although not really material until you get to very large datasets.

I do accept the logic of effectively storing the calculation in one place so it can be used anywhere (SQL/VBA)

 

[vbaInet]

Calling a complicated UDF will be slow of course, but when comparing a UDF that uses an IF statement and a built-in function that uses an IIF() function, there are time savings when you use a UDF. If I get time I'll set it up and show you the test results.