none
Help on Access RRS feed

  • Question

  • I am working on MS Acess and want to set a field by the rule below

    1. when a date has been captured on the database on [Date of Payment]. it should give it the score of 5, after the first  90days it should score it 4, after another 180days it should score it 3 and after 180days it should score 2. then after 360 it should score it 1


    Saturday, January 19, 2019 4:46 PM

All replies

  • You could create a calculated fields. Personally though I avoid them and would simply create an update query to apply the rule and run the query when the database is opened using an AutoExec macro.  And/Or, use the form's current event to apply your rule when the record is viewed.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Saturday, January 19, 2019 6:05 PM
  • I am working on MS Acess and want to set a field by the rule below

    Hi FreshPrince,

    An other thought …

    Instead of storing a "score" - determined by arbitrary boundaries - you could also store the days difference between the two dates. Though it is a calculated field, it is for the user a better performance indicator then the two dates where it is calculated from.

    The nice thing is that you can easily sort on the on this field and see that 179 days (score 4) is not that much better than 181 days (score 3).

    Any "score" can be retrieved with the proper range of these difference-days.

    Imb.

    Saturday, January 19, 2019 7:48 PM
  • The ranges which determine the scores are data.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  Your figures are a little confusing as 180 days is mentioned twice, but I'm assuming each range is of 90 days, in which case you'd need a Scores table as follows:

    Score    LowerLimit
    1           360
    2           270
    3           180
    4           90
    5           0

    You can then easily return the score, in a computed column in a query or a computed control in a form or report, with the following function, into which the payment date is passed as its argument:

    Public Function GetScore(varDateOfPayment As Variant)

        Dim strCriteria As String
        
        strCriteria = "LowerLimit <= " & varDateOfPayment - VBA.Date()

        If Not IsNull(varDateOfPayment) Then
            GetScore = DMin("Score", "Scores", strCriteria)
        End If
        
    End Function

    You can see how it works by entering some literal values in the immediate window, which, as of today, would give the following results:

    ? #2019-02-20# - VBA.Date, GetScore(#2019-02-20#)
     31            5
    ? #2019-04-20# - VBA.Date, GetScore(#2019-04-20#)
     90            4
    ? #2019-07-20# - VBA.Date, GetScore(#2019-07-20#)
     181           3
    ? #2019-10-20# - VBA.Date, GetScore(#2019-10-20#)
     273           2
    ? #2020-01-20# - VBA.Date, GetScore(#2020-01-20#)
     365           1  

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Sunday, January 20, 2019 2:27 PM Clarification
    Sunday, January 20, 2019 2:24 PM