Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Get a Next Deposit Date depending on criteria?

    ProfileAccount.zip


    Hi all,
    Trying to figure out a query or multiple queries that will provide me with a Next Deposit Date. This same thing
    will be used in different forms but just this for now till I get some type of solution. Maybe someone has something
    I can use to get started?

    So, db attached, opens to Form1 which is the form with the controls on it. I programed the controls for the
    most part and will tweak code once complete. What I have not done is the Next Deposit Date...


    On Deposit Frequency section,
    the Select has 5 options in the combo which is a Value List
    1;"Weekly";2;"Monthly";3;"Bi-Monthly";4;"Annually";5;"Semi-Annually"


    Then it has FirstDay, SecondDay, FirstMonth, SecondMonth Fields


    So, what I would like to do is:


    If Select "Weekly" No Next Deposit Date Period! Just leave Enabled = False


    If Select "Monthly" and FirstDay is not null, then create a date one month or 30 days in advance from that day.
    Basically if Today is 07/11/2022, and my FirstDay # is 15, then then My NextDepsoitDate would be 15/11/2022. If
    Today was 25/11/2022 and FirstDay # is 15, then my NextDepsotDay would be 15/12/2022 and so on....




    If Select "Bi-Monthly and FirstDay and SecondDay are not Null, then create a date that is one month or 30 days from that
    day. If Today is => SecondDay then use that Field "FirstDay" # as the day or If Today is >= FirstDay then use
    the Field "SecondDay" # as the day


    Basically if My FirstDay was the 15 and My SecondDay was the 31, then If today was between the 31st and the 14th, then it would show the
    Next Depsoit Day being the 15th, If today was between the 15th and the 30th, then it would show my Next Deposit Day the 31st.




    If Select "Annually" and FirstDay and FirstMonth are not Null, Then Create a date that is on year from those two fields
    , one being a day and one being a month.


    So, If I select Annually, and my FirstDay is the 7th and the Month is 11 and if today is
    11/12/2022 then my Next Deposit Date would be 7/11/2023


    If Select "Semi-Annually" and FirstDay, FirstMonth, SecondDay, SecondMonth were not Null, then create a date! If Today
    >= FirstDay, FirstMonth but <= SecondDay, SecondMonth then the SecondDay,SecondMonth Fields would be used to creat
    a Next Deposit Date and If Today >=SecondDay, SecondMonth but <= FirstDay, FirstMonth then the FirstDay,FirstMonth Fields would
    be used to create Next Deposit Date.


    If Today is 7/11/22, and my FirstDay Is 6, First Month is 3, and my SecondDay is 6, And the SecondMonth is 9, then
    My Next Deposit Date would be 06/09/22




    Questions on the following:
    What I am not real sure about is how that will react if a month has less then 31 days????????

    Thanks
    Dave

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    You have the logic.
    Write a function with that logic.
    Pass in the values, and return required date.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    To add to Welshgasman's post- you will need to use DateSerial(), DateAdd() and some sort of date comparison function (to compare the first day with today's), here is what I have to compare dates\numeric values:
    Code:
    Public Function Min2(a As Variant, b As Variant) As Variant
    '
    ' Returns the lesser of 2 values
    '
      Min2 = IIf(a < b, a, b)
    End Function
    Public Function Max2(a As Variant, b As Variant) As Variant
    '
    ' Returns the greater of 2 values
    '
      Max2 = IIf(a > b, a, b)
    End Function
    I am also including an old module I've been using for many years with lots of very usefull date functions, hopefully it would help.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you Gicu,
    That should give me some direction
    Dave

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Welshgasman,
    If it were that simple! LOL
    Dave

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Dave, once you get the main statements going post back with what you have and we'll see how we can help.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by d9pierce1 View Post
    Hi Welshgasman,
    If it were that simple! LOL
    Dave
    Well, it does not have to be that efficient.
    Just take one test at a time and exit sub if true.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you so much

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    ProfileAccount.zip

    Hi all, Know it has been quite a while however I have been shuffeled around the states fixing problem jobs so have not had a lot of time to do this.
    I was able to get it to work but by no means as I wanted. I really got stuck with the query. I was able to do in code and I had to put the calculated field in the table and use a refresh button to update this.
    I know not good to have calc field in table so maybe I can get a little assistance now that I have it working? I just got really lost and couldnt get it in a query.

    Thanks so much,
    Dave

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you go Dave, you were almost there, took your sub and made it a public function in a standard module so you can call it from a query.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thank you so much! Will do it!
    Dave

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Already done ....
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    ProfileAccountVlad - Copy.zip

    Hi Gicu
    I have a couple of questions on the function.

    First: I ran into a Invalid use of Null error when selecting "Weekly" so I just put exit sub on it.
    After thinking about it, i though I may need that weekly. So, I changed function a small bit but I am having
    hard time getting it to calculate correctly for week day. I put a combo on form "CboWeekDay" and made it a value list, 1 - 7 and days of week Sunday being first.
    I have done some reading and looked over some examples but still stuck.... I know what I did for Weekly is not correct but close I believe. Would love some help with it
    if you have a minute....?

    Second Question is how hard will it be if a month don't have 31 days like this month, to change calculations? Such as I deposit on 1st and 31st but if there are not 31 days
    in the month such as this month it would be the 28th. Currently it add a month I guess so my next deposit date is 3/3/2023 instead of 2/28/2023 if that makes any sense?
    I could simply just change date to 1st and 15th but that may confuse a user! Any suggestions, I included this db with my changes on this....
    Thanks
    Dave

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Dave,
    Would you please explain the desired outcome for the Weekly option?
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Dave,
    Here is the updated file to deal with your second issue, it will put the last day of the month if the second day is greater than the end of month. As for the weekly I don't know what you want to do, can't really see the logic in this:

    fnNextDepositDate = DateAdd("d", 18, DateSerial(Year(Date), Month(Date), iCboWeekDay))

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 08-23-2018, 01:23 PM
  2. Replies: 2
    Last Post: 03-13-2018, 04:26 AM
  3. Replies: 1
    Last Post: 11-18-2014, 11:19 AM
  4. Replies: 1
    Last Post: 09-04-2014, 10:13 AM
  5. Replies: 25
    Last Post: 11-16-2012, 12:47 PM

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