Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-14-2019, 06:37 PM   #1
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Calculating Good Conduct Allowance for inmates

Hi,

I have a slight problem, I have an excel doc where I need to calculate an inmates Good Conduct Time Allowance between some dates (GCTA)

If that was all I already managed to do it but... There are some caveats in Oct 2013 the rules changes and the GCTA amount changed. I did all of this already in a vba scrip and it all worked fine but then I found out there was another parameter that messed up my whole calculation again...


So if anyone has the time to give me a push in the right direction here comes a short explanation of the calculation.

General variables
TodayDate = Jan 2019
NewLawDate = Oct 2013

Inmate properties
DateDetained = Jan 2000
DateFinalSentence = Jan 2004


Now...below you see the difference between the old law and the new law for days given to an inmate per month after he has been detained for a number of years.


Old law until October 10th 2013
5 days for each month for first two years
8 days for each month 3rd to 5th year
10 days for each month 6th to 10th year
15 days for each month 11th year and on

New law after Oct 10th 2013
20 days for each month for first two years
23 days for each month 3rd to 5th year
25 days for each month 6th to 10th year
30 days for each month 11th year and on

Untill now it was all good, I check what bracket the inmate falls into and do some adding.

But then I found out that the GCTA doesn't go into affect until the inmate has a finalized date for the sentence meaning GCTA starts being added from the day of the dateFinalSentence but the GCTA "bracket" is calculated from the dateDetained

So for our inmate above he falls into the 4year bracket of the old law to start calculating the GCTA so he skips the 5 days a month and starts at 8days a month directly as he has already been detained 4 years

Then from Jan 2004 until Oct 10 2013 he gets the old law GCTA and then after that he gets the new law GCTA.

It all went south for me when I tried offsetting the calculation with the dateFinalSentence...

Any idea on how to approach this little gem I'm all ears

Cheers!

__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-14-2019, 08:39 PM   #2
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

I would create two lookup tables, maybe one, if the Date is not associated with any other peripheral data.

Create a lookup table to record data like so,

Code:
tbl_GoodBehaviourDays

  GB_ID       pk
  Date        date
  YearLower   integer
  YearUpper   integer
  Days        integer
  
  
  GB_ID   Date      YearLower   YearUpper   Days
  1       10/8/13           1           2                5
  2       10/8/13           3           5                8
  3       10/8/13           6           10             10
  4       10/8/13          11         99              15
  5       25/3/17          1           2               20
  6       25/3/17          3           5               23
  7       25/3/17          6           10             25
  8       25/3/17         11         99              30
If the Date field has other associated data, then that field would be FK to a master table, that records the date along with other associated data.

On preview, apologies for the formatting, but I assume you get the idea. When laws change, new 'date as of' with updated days, even different year brackets, all that is required is to enter the information. There would not be any need to change your form or query design. This is only one part of your particular issue. Hopefully it puts you on the right track.
__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
Old 03-14-2019, 09:05 PM   #3
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

First of all many thanks Steve for even getting through my post thst wasn't that easy to decipher. I get what you mean by the lookup tables but I've never used them really (will look them up) in access before I always used actual tables etc never these vba ones.

Anyhow let's say I create these and then I can lookup the days for each year bracket. Depending on what year the inmate is on. My precious vba code was really simple using if/elseif statements for each bracket and in each I calculated the corresponding days. Your way is of course the much better way and dynamic to calculate this but I can't figure out the logic of the actual calculation.

The input info for an inmate will only be 2 dates, dateDetained and the dateFinalizedSentenced

Then the output would be amount of days of GCTA calculated from his dateFinalizedSentece until today based on his dateDetained and of course old and new law values for each month..

Any way you could roughly guide me in how to combine your lookup tables and the calculation/formula in vba?

I'm not asking you to code it all of course I'm just to much if a novice to not code totally procedural and simple haha

Cheers

__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-14-2019, 09:17 PM   #4
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Sorry Steve, you meant look up tables as in just a separate sheet thst I reference right? I've not really used excel before for vba and forgot thar you can create tables in them just like you create tables in Ms Access... Haha but im still not clear on the logic and actual vba calculation I mentioned above...

PS. In my original script I based all on months as it's days per month I add etc so I made the brackets 1-24,25-60 etc.. And I used datedif to get difference in months between dates in vba and manages somehow to calculate my first time around but like mentioned after the offset paramatee date it got to messed up trying to keep track of it...
__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007

Last edited by zozew; 03-14-2019 at 09:24 PM.
zozew is offline   Reply With Quote
Old 03-14-2019, 09:34 PM   #5
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Quote:
Originally Posted by zozew View Post
Sorry Steve, you meant look up tables as in just a separate sheet thst I reference right? I've not really used excel before for vba and forgot thar you can create tables in them just like you create tables in Ms Access... Haha but im still not clear on the logic and actual vba calculation I mentioned above...

PS. In my original script I based all on months as it's days per month I add etc so I made the brackets 1-24,25-60 etc.. And I used datedif to get difference in months between dates in vba and manages somehow to calculate my first time around but like mentioned after the offset paramatee date it got to messed up trying to keep track of it...
I've just picked up from your original post and reminder above that you were referring to an Excel document. I really should learn to read slower; I assumed it was Access related. As regards Excel I can't offer any advice as I don't use it other than the basic stuff.

FWIW, what you are describing is best suited to a database (in this case, Access) and not a spreadsheet, just my opinion. Overtime spreadsheets become convoluted; I see it all the time where I work.
__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
Old 03-14-2019, 09:49 PM   #6
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

No worries Steve, but to be honest the way vba works on Access and excel is almost exactly the same as I see it.

So for arguments sake now that I got your attention on this subject 😉 if you would do this in access vba, the calculation how would you calculate it. I'm pretty sure I can replicate it in excel if I just get started some how..

Cheers
__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-14-2019, 10:06 PM   #7
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Quote:
Originally Posted by zozew View Post
No worries Steve, but to be honest the way vba works on Access and excel is almost exactly the same as I see it.

So for arguments sake now that I got your attention on this subject 😉 if you would do this in access vba, the calculation how would you calculate it. I'm pretty sure I can replicate it in excel if I just get started some how..

Cheers
Ok, leave it with me, I'll knock something up. My initial thought is to create a function, GetDays(DateStart, DateEnd) that returns the total number of days of good behaviour (if criminals are in fact of good behaviour). I'll address it over the weekend.

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
Old 03-14-2019, 10:19 PM   #8
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Haha, You are an angel!

Cheers again and if this works I'll make sure to get you a few beers somehow
__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-15-2019, 03:09 AM   #9
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Here's something that you can work with, study, improve. In the first instance, it may not address all your requirements but it should give you an idea of how to proceed. In summary (the db is attached) I created two lookup tables (with limited info at hand, it could easily be one table, but two tables does not complicate things).

From the Immediate Window:
Code:
? GetDays(#3/2/2010#,#11/11/2015#)
Date: 2/03/2011 Year:  1    ID : 1  DaysAlloted:  5
Date: 2/03/2012 Year:  2    ID : 1  DaysAlloted:  5
Date: 2/03/2013 Year:  3    ID : 2  DaysAlloted:  8
Date: 2/03/2014 Year:  4    ID : 6  DaysAlloted:  23
Date: 2/03/2015 Year:  5    ID : 6  DaysAlloted:  23
 64
The function is as follows:
Code:
Public Function GetDays(pDatStart As Date, pDatEnd As Date) As Integer
On Error GoTo Error_In_Code

    ' assumption, pDatStart is before pDatEnd
            
    Dim intReturn As Integer
    Dim db As DAO.Database
    Dim rs As Recordset
    Dim rsDates As Recordset
    Dim datTrack As Date
    Dim intRunningTotal As Integer
    Dim intTotalYears As Integer
    Dim k As Integer
    Dim lngDateID As Long
        
    Dim strSQLDates As String
    Dim strSQLBracket As String
    Dim strWhere As String
    
    Dim booRecordFound As Boolean
    booRecordFound = False
        
    ' queries written on the fly, this orders the tbl_Lookup_Date in descending order
    strSQLDates = "SELECT GB_Date_ID, DateStart FROM tbl_Lookup_Date ORDER BY DateStart DESC;"
    
    ' how many years do we need to check
    intTotalYears = DateDiff("yyyy", pDatStart, pDatEnd)
                    
    Set db = CurrentDb
    Set rsDates = db.OpenRecordset(strSQLDates)
    
    intRunningTotal = 0
        
    For k = 1 To intTotalYears
        'reset record found flag to false
        booRecordFound = False
        ' last record or latest date first, oldest date is last
        rsDates.MoveFirst
        datTrack = DateAdd("yyyy", k, pDatStart)
        
        Do While Not rsDates.EOF
            If datTrack >= rsDates("DateStart") Then
                lngDateID = rsDates("GB_Date_ID")
                ' set record found flag to true, below code will be performed
                booRecordFound = True
                Exit Do
            Else
                ' go to next record, a date older
                rsDates.MoveNext
            End If
        Loop
        
        ' below only works if a record was found.   A record not found could be if an entered date precedes the
        ' DateStart date in tbl_Lookup_Date
        If booRecordFound Then
                    
            strWhere = "WHERE GB_Date_ID = " & lngDateID & " AND (YearStart >= " & Str(k) & " OR YearEnd >= " & Str(k) & ")"
            strSQLBracket = "SELECT * FROM tbl_Lookup_Days " & strWhere & ";"
                    
            ' assumption, only one record returned
            Set rs = db.OpenRecordset(strSQLBracket)
            rs.MoveFirst                                ' not needed but I seem to always include this
            Debug.Print "Date: " & datTrack & " Year: " & Str(k) & "    ID :" & Str(rs("GB_ID")) & "  DaysAlloted: " & Str(rs("DaysAlloted"))
    
            ' accumulate the DaysAlloted
            intRunningTotal = intRunningTotal + rs("DaysAlloted")
        End If
            
    Next k
    intReturn = intRunningTotal

Exit_Function:
    GetDays = intReturn
    Exit Function
    
Error_In_Code:
    intReturn = -1
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_Function
End Function
Attached Files
File Type: accdb GoodBehaviour.accdb (616.0 KB, 3 views)
__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
The Following User Says Thank You to essaytee For This Useful Post:
zozew (03-15-2019)
Old 03-15-2019, 02:22 PM   #10
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Looks fantastic! I'm going to open it and go through it and understand the logic I see that you are quite unlike me 😂 you actually know how to code properly.

I think you might have a couple of beers coming in the mail 😁 🍻

Cheers
__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-15-2019, 03:59 PM   #11
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Quote:
Originally Posted by zozew View Post
Looks fantastic! I'm going to open it and go through it and understand the logic I see that you are quite unlike me 😂 you actually know how to code properly.

I think you might have a couple of beers coming in the mail 😁 🍻

Cheers
Appreciate your comments. For the table that holds the 'DateStart' field and nothing else, that could be further expanded that shouldn't interrupt your current task of figuring out the number of days. Additional field(s) could be related to what act of parliament created the 'days of good behaviour'. That sort of information doesn't need to be replicated over and over again in the 'days' table.

The point I'm making is that if you get the table design structure correct in the first place, it saves a lot of headaches down the track.
__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
Old 03-15-2019, 07:19 PM   #12
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Hey again!

I had a blast this morning, I haven't used access since I made an amateurish but rather complex database 5 years ago. I installed access again and managed after some hairpullimg to run your code and understand the function.

So first of all like I memtioned before very nice and proper code makes me wonder how I ever managed to get my dB working haha.

So to the function I see thet you misunderstood a small part but that's probably my fault. You are basing the GCTA days on years they are actually given for every month.. So year 1 - 2 of the old law gives 5 days per month so a total of 24x5 and so on 😊 that's why I based my old vba script on the months for each bracket so first 1-24 months , second 25-60 months , third 61-120 months , last 121~more months

Question... Because the days alloted for the old an new law are not going to change at all for probably the duration this dB or excel doc will ever be used could or wouldn't it be more practical to hard code the days alloted per month and just have the start date, end date and the offset date ( when the GCTA should start to be added up) for the function?

I'm thinking.. if it's all in vba apart from the 3 dates mentioned above... Porting it to excel vba would be a breeze ( I hope)

I've added a very artistic photo of a timeline for our inmate Joe Schmoe where start date is long before the offset date but the offset date is alse before the new law date. So the GCTA days should be added from the offset date and forward but the bracket is according to the start date.

Anyhow I really enjoued wrapping my head around Access again I even open my old dB from 5 years ago and looked at some code I made... Totally ridiculous but fun to see that it actually worked for a while 😁

Cheers!
Attached Images
File Type: jpeg received_646832389104904.jpeg (25.3 KB, 11 views)
__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-15-2019, 07:55 PM   #13
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

No problems. At the moment all I can suggest are some tips (suppose to be out in the garden, gardening, that's what my other half wants me to do).

Quote:
Originally Posted by zozew View Post
So to the function I see thet you misunderstood a small part but that's probably my fault. You are basing the GCTA days on years they are actually given for every month.. So year 1 - 2 of the old law gives 5 days per month so a total of 24x5 and so on 😊 that's why I based my old vba script on the months for each bracket so first 1-24 months , second 25-60 months , third 61-120 months , last 121~more months
To account for this, very little modification required in the function, multiply the Days Allotted by 12. That "12" could/should be a constant variable (not required, but that's what I would do). I'll leave that with you, give it a try.

Quote:
Originally Posted by zozew View Post
Question... Because the days alloted for the old an new law are not going to change at all for probably the duration this dB or excel doc will ever be used could or wouldn't it be more practical to hard code the days alloted per month and just have the start date, end date and the offset date ( when the GCTA should start to be added up) for the function?
No need to hardcode anything. You will see I have entered two entries the 'Dates' table. To overcome, whatever happened many years earlier, and most likely not going to be a part of current calculations, make a new entry, the date, very old (1/1/1900). In the days table, create one entry referring to the 1/1/1900, the YearStart as 1, Year End 100, Days Allotted 5 (or whatever). This is your hardcoding entry, in the Table, and not in code. Just a thought.
__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
The Following User Says Thank You to essaytee For This Useful Post:
zozew (03-16-2019)
Old 03-15-2019, 08:28 PM   #14
zozew
Newly Registered User
 
Join Date: Nov 2010
Posts: 211
Thanks: 16
Thanked 1 Time in 1 Post
zozew is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Go go to the garden haha, it's Saturday and code should be far from mind of you have a significant other around ��

I'm so happy and grateful you could land a hand ( you basically did it all)

I on the other hand have no better half around so I'll have a look at your suggestions and update a little probably tomorrow and if you happen to chelc your email ( you shouldn't) you can steer me in the right direction again ��
__________________
_________________________________
Old Flashdeveloper trying out the MS world
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Win XP & Access 2007
zozew is offline   Reply With Quote
Old 03-15-2019, 09:56 PM   #15
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 407
Thanks: 8
Thanked 80 Times in 77 Posts
essaytee is on a distinguished road
Re: Calculating Good Conduct Allowance for inmates

Quote:
Originally Posted by zozew View Post
Question... Because the days alloted for the old an new law are not going to change at all for probably the duration this dB or excel doc will ever be used could or wouldn't it be more practical to hard code the days alloted per month and just have the start date, end date and the offset date ( when the GCTA should start to be added up) for the function?
...snip...
I've added a very artistic photo of a timeline for our inmate Joe Schmoe where start date is long before the offset date but the offset date is alse before the new law date. So the GCTA days should be added from the offset date and forward but the bracket is according to the start date.
...snip...
Cheers!
You might have to explain the process further. This is how I interpret it so far:

1. Detained Date (person is on remand, has not been convicted or sentenced). Therefore, may be released if found not guilty.

2. Sentenced Date (person sentenced to a number of years, whatever). Sentenced Date will always be >= Detained Date. Person may have been convicted at court but was not detained at that point, but is detained on Sentence Date.

3. End Date (Is this the last day of imprisonment of the prisoner? Is it purely a date (today) in order to work out how many Good Character days have elapsed? Could it be a future date, again to calculate expected Good Character days?)

From your diagram, no consideration is to be given to the period of time from Detained Date to Sentence Date.

My conclusion based on the above: When using the function, do not pass in the Detained Date, pass in the Sentence Date.

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is online now   Reply With Quote
The Following User Says Thank You to essaytee For This Useful Post:
zozew (03-16-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add sub form in Report to calculate (discount, Travel allowance...) Zinger Reports 1 10-22-2015 12:58 PM
Field Criteria, need to add an allowance of xy tinyevil777 Queries 12 02-25-2011 11:55 AM
Use Query Results onLoad of Form to conduct an Action airforceruss Queries 6 01-03-2008 05:10 PM
[SOLVED] SendObject Good but not that Good... Hazmataz Macros 7 11-13-2002 07:30 AM
Say Good morning, Good afternoon! Sohaila Taravati Forms 4 02-22-2002 07:49 AM




All times are GMT -8. The time now is 01:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World