Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62

    Exclamation Please help what is the code for automatic penalty!!!

    good day people,I am building a water billing system,i want my penalty column to automatically calculate the amount due.

    this is my sample table

    date billed due date amount due date paid or number amount paid total balance


    09/10/2018 09/25/2018 150.00

    1st question),the above says the due date is 9/25/2018 so i want my Penalty column to check if the date paid is less than or equals to due date,then the penalty displays 0.00 but if date paid is greater then due date then it will display (amount due*0.05),,2nd code if the customer did not yet pay or the date paid or column is blank, the penalty column will compare the computer date(today()) if computer date is greater than due date then penalty displays (amount due*0.05) if false,it displays (amount due*0.05).
    the 3rd question is how will the second code would stop execute if the date paid is filled with data?

    thank you in advance!!
    I hope you will anwer my post!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Use an expression in a query. Something like:
    Code:
    TotalDue: IIf(([datepaid] Is Null Or [datepaid]>[datedue]) And [datedue]<Date(),[Amount]+[Amount]*0.05,[Amount])
    See attached db for simple example.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62
    Thank you so much sir, for this.you have the answer.

    I have tried it and this is it!

  4. #4
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62

    How to Forward data from other column to subtract to next column.

    I would like to continue my question here sir/maam.,

    (since I am building & just starting my Water Billing System)I have a problem when entering a Previous Reading-Present Reading=Consumption in my Transactions Table.

    sample here

    1st column (tblTransactions)

    Month DateBilled Previous Reading Present Reading Consumption
    Jul-18 8/16/2018 14 16 2

    so in the next column, still in the tblTransactions sample the Next Reading is in a month, I want a code to automatically forward or copy and put the Present Reading to become the Previous Reading in the Next Column and again subtract it.

    2nd column

    Month Date Billed Previous Reading Present Reading Consumption
    Aug-18 9/16/2018 (automatically)16 18 2

    I hope you could help me to code with this. Thank you in advance.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend you show readers a jpg of your tables and relationships with all tables expanded to show all fields.

    Here are a couple of links that may be useful to you.
    Stump the model
    Subquery (value in another record)

    I also recommend you do not use a naming convention that allows/uses embedded spaces in field/object names. Use only alphanumeric characters in names.

    Good luck.

  6. #6
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62
    This is the actual image of my Water Billing System.
    by the way I'm just a newbie in MS Access, but I have experienced in VB programming.Click image for larger version. 

Name:	relationship form.jpg 
Views:	57 
Size:	126.8 KB 
ID:	35504
    Click image for larger version. 

Name:	reading form.jpg 
Views:	56 
Size:	158.5 KB 
ID:	35505 I hope this will clear what I want to do with my access application. Hope you can help. Thank you

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have several naming errors - IMHO.
    These are the errors

    Click image for larger version. 

Name:	ObjectNames1.jpg 
Views:	49 
Size:	73.2 KB 
ID:	35518


    Naming suggestions:
    ------------------------------------------

    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names



    To get the previous reading, it might be easier to write a UDF.

  9. #9
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62
    @ssanfu-- why UDF? and what i would use to make UDF file?

  10. #10
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62
    @orange -Thanks for the link I might study the flow of this relationship.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry, UDF means a user defined function.

    You write a function that would return the previous read value based on a date.

    This is UNTESTED, but it would look something like this:
    Code:
    Public Function fnPrevRead(pPrevDate As Date) As Integer
        'needs error handling code
    
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        'default return value
        fnPrevRead = 0
    
        'get the previous read value by billed date
        sSQL = "SELECT TOP 1 Previous FROM Transactions"
        sSQL = sSQL & " WHERE DateBilled < #" & pPrevDate & "#"
        sSQL = sSQL & " ORDER BY DateBilled DESC"
        '    Debug.Print sSQL
        Set r = CurrentDb.OpenRecordset(sSQL)
    
        If Not r.BOF And Not r.EOF Then
            fnPrevRead = r.Fields(0)
        End If
    
        'clean up
        r.Close
        Set r = Nothing
    End Function

  12. #12
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    I use spaces in my tables names. I guess I'm lazy.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by JSR View Post
    I use spaces in my tables names. I guess I'm lazy.
    You're not lazy enough. The really lazy person doesn't use them so they don't have to type the brackets around them all the time.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    (I meant to say table names and field names)

    Old habits die hard. I read the help files many years ago with examples having brackets around field names, so I always used brackets in VBA code, unaware that it was unnecessary if your field name doesn't have a space in it.

  15. #15
    markpastoril is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    62
    Hello sir, follow up question, what if I dont want to impose penalty if the the due date is on friday or weekends(saturday and sunday) . I want my code to do like this if due date is friday then the penalty will impose in monday and then when due date falls saturday and sunday , due date will move forward to monday. could you help me with this sir?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-10-2017, 05:00 PM
  2. Getting a non-automatic ID to become AUTOMATIC !
    By THE STUDENT in forum Access
    Replies: 3
    Last Post: 06-12-2013, 02:01 AM
  3. Replies: 3
    Last Post: 02-22-2013, 06:41 AM
  4. Automatic backup
    By timpepu in forum Access
    Replies: 4
    Last Post: 08-14-2012, 01:38 PM
  5. Automatic Flagging
    By terricritch in forum Access
    Replies: 4
    Last Post: 09-14-2010, 06:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums