Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Is it possible to use IF/THEN in a query to calculate a conditional total

    I have a query through which I raise invoices, but now we find it necessary to charge clients interest who do not pay on due date.

    I do not know how to code or write the IF/THEN function in a query to charge 10% interest on the total owed from due date. For example:

    Client Name: ABC Total Owed: £50 Invoice Date: 01/11/08 Due Date: 01/12/08


    Now if payment is now paid on 01/12/08, we want to raised another invoice which should automatically add and calculate 10% on the invoice total which of course will be a new invoice (meaning on new report).

    Any help would be grately appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmm - your example charges 10% for on-time payment!

    check out IIF() in help - works fine (but slow) in a query
    result: iif(someDate > otherDate, 1.1 * originalPrice, originalPrice)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are going to raise another invoice then it has to be a VBA process... I think
    depending on how your invoices are generated you may be able to do it as an insert query. if you gave a single invocie item and not an invoice header and child invoice items
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2004
    Location
    Connecticut
    Posts
    85
    Having been forced to work with Accountants for extended periods of time, I can tell you that they get their panties in a bunch (knickers in a twist) about Invoices - the invoice date determines how and when the charges show up on the balance sheet, so when you redo an invoice, you have to use the original invoice date and invoice number and put giant marks on it that tell them that it's not a new invoice, but a bill referring to the original invoice.

    In the US, there's usually a set of terms printed on the invoice (i.e. NET10, or NET30) to tell the customer that you will charge the interest rate in the terms if the invoice isn't paid on the due date - it's usually 1.5% per month. NET10 means the customer has a grace peiod of 10 days. NET30 gives the customer 30 days to process the invoice and generate a check before interest starts to accrue.

    For you, if they don't pay by the due date, they get charged an extra 10%. You don't say what happens after that, so we'll assume that it's a one-time charge.

    When you run the invoice the first time Total_Owed=Sum(Charges)

    When you reprint them, Total_Owed=iif(DueDate<Date(),Sum(Charges)*1.1,Sum (Charges))

    "Date()" is the expression for today's date, which is why you shouldn't name any field "Date" in MSAccess.

    Just to prove that I didn't waste all my time in business school, the terms can be negotiated as part of the contract, or the vendor (your company) can apply the same terms to every invoice. The 1.5% per month has been the standard B2B interest rate that keeps the economy moving for decades. Nobody will lend a customer actual money to buy stuff, but the charges will keep adding up if you don't pay on time - that's why you want the customer's credit rating before you sell him stuff. When invoices get paid on time, everybody's happy. When you don't pay me, I can't pay my suppliers and I need to get money from somebody else. If I'm lucky, you eventually pay up with enough interest to let me pay my bills.
    Last edited by trowe; 12-03-08 at 12:38.

  5. #5
    Join Date
    May 2006
    Posts
    386
    First of all, a very warm thanks to Izyrider, healdem and Trowe for your detailed and comprehensive information.

    Let me explain this in a very simple term. Basically we give clients 30 days to pay our invoice, if they have not paid our invoice by the ExpectedPaymentDate, then I want the database to charge 2% interest (not 10% as it was an example only) on the ClientAmountLessVAT IF the InvoiceDate is greater than 50 Days.

    I have used the suggested expression from Izyrider as follow but it is not calculating it correctly: Please correct me if I have to change anything:
    InterestCharge: IIf([InvoiceDate]>50,1.1*[ClientAmountLessVAT],[ClientAmountLessVAT])
    Also, please note, I do not recreate another invoice, I want the same invoice to remain as is adding the new interest charge which of course I will show with a different color on my invoice (report).

    Thank you so much for your help.
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    [InvoiceDate]>50

    That part seems wrong.

    Try this:

    InterestCharge: IIf(Date() - [InvoiceDate]>50,1.02*[ClientAmountLessVAT],[ClientAmountLessVAT])

    Probably should use DateDiff, but I am lazy today
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2006
    Posts
    386
    Thank you, I will give it a go and will update you of the progress.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    May 2006
    Posts
    386
    Hi again, I have tried this
    InterestCharge: IIf(Date() - [InvoiceDate]>50,1.02*[ClientAmountLessVAT],[ClientAmountLessVAT])
    and changed it to
    InterestCharge: IIf(Date() - [InvoiceDate]>50,1.02*[ClientAmountLessVAT]-[ClientAmountLessVAT])
    so rather than calculating the interest charge on top of the ClientAmountLessVAT it should show the InterestCharge only which is working fine.

    But when I run the query on itself or on a Form or Report, where clients have not incurred any interest charge it is leaving the filed (InterestCharge) blank which means my calculations on Forms and Reports don't work as they cannot accept Blank fields. However, when I change the code back to this:
    InterestCharge: IIf(Date() - [InvoiceDate]>50,1.02*[ClientAmountLessVAT],[ClientAmountLessVAT])
    meaning remove , and add - then it adds 0 in every field. But this does not show me the InterestCharge only it shows the InterestCharge+ClientAmountLessVAT. Would you please help me how to make sure that even if there is no InterestCharge the field should be 0 by default. For your information, I have set the Default Value for this field as 0 and I have also manually added 0 in all records so every single record of InterestCharge has a 0 in it.

    Your advice would be much appreciated.
    Last edited by Emal; 12-06-08 at 08:07.
    Emi-UK
    Love begets Love, Help Begets Help

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you looked at the IIF function and understood what it does.. try selecting the iif function and select help on that function (press F1).

    what happens if the organisation decides to change the interest rate payable on late invoices (after all a 2% interest charge isn't that significant)...

    Id suggest you use datediff in place of your current construction
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2006
    Posts
    386
    I understand the basic functions of IIF but not much to be able to write complext codes/syntax. In terms of if th organisation changes interest rate, then I assume by increasing the 1.02% it should do the job.. OR do you think there are other complications?

    Would you please advise how datediff works and kindly give an example. I had used datediff probably a few years ago with one particular small db but I honestly don't remember much about its full function.

    Thank you for your time.
    Emi-UK
    Love begets Love, Help Begets Help

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so did you resolve your problem with your IIF statement?
    what did the help system suggest.......

    as regards how datediff works....have a look in the help system.. the help function may be a bit crappy, especially comapred to earlier versions but its till a goldmine of information

    what you are doing probably is fine for dates in Access/JET (which uses the integer part of a datetime value to indicate days, the decimal part the proportion of a day) , but the suggestion for using datedif is to make future code more compatible shoudl you chnage the back end datastore

    I think you have lots of potential issues
    you are hardcoding values which may change which is a definite no no.....
    VAT has just changed
    incidentally Im not to sure where you stand charging VAT on late payment penalties

    I don't thin creaitng this penalty of tne fly is correct.. for accounting reasons I'd expect it to be inserted into a new invoice to be included ont he next statement
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2006
    Posts
    386
    Hi Healdem, yes, I have resolved the IIF statment issue. I actually worked it around on Forms and Reports by using some multiplying and subtractions some figures. It serves my purpose at the moment.

    I have tried to find some help for Datediff in the help fuction but it did not suggest anything when I searched for Datediff.

    However, there is only one thing at the moment that I am struggling with and if that works then the whole problems will resolve and that is how to lock a date. I have posted a new thread regarding this and I hope someone would be kind enough to help me amend the code.
    Emi-UK
    Love begets Love, Help Begets Help

  13. #13
    Join Date
    May 2006
    Posts
    386

    Need help on how to calculate Interest and Late Payment Fee in a query

    Dear All,

    I am currently using the following code in a query for charging interest which is working fine but not what my boss has asked me to do.

    InterestCharge: IIf(Date() - [InvoiceDate]>50,1.02*[ClientAmountLessVAT],[ClientAmountLessVAT])
    Basically, I want the InterestAndLatePaymentFee to be charged as following:

    if invoice is date is greater than 40 days, then InterestAndLatePaymentFee should be charged as 15%, then for every other 10 days in addition to the first 40 days, an extra 5% InterestAndLatePaymentFee should be accrued to the invoice.

    Any help would be highly appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •