asked on Rolling Qtrs using vb6 classic
I need to change the below logic to take into consideration what quarter the current date is
here is the logic i need
the below is just by months and needs to be by quarters with the above table as reference
' lvmain1 date is mmddyyyy ie 01202020
For zzz = LVMain1.ListItems.Count To 1 Step -1
DDate = LVMain1.ListItems(zzz).SubItems(10)
CDDate = Left(DDate, 2) & "-" & Mid(DDate, 3, 2) & "-" & Right(DDate, 4)
If (Year(Now) * 12 + Month(Now)) - (Year(CDDate) * 12 + Month(CDDate)) > 11 Then
LVMain1.ListItems.Remove (zzz)
End If
Next zzz
here is the logic i need
| Month | Time frame to keep | Months |
| Jan 2021 | Apr 2020 to Jan 2021 | 10 |
| Feb 2021 | Apr 2020 to Feb 2021 | 11 |
| Mar 2021 | Apr 2020 to Mar 2021 | 12 |
| Apr 2021 | Jul 2020 to Apr 2021 | 10 |
| May 2021 | Jul 2020 to May 2021 | 11 |
| Jun 2021 | Jul 2020 to Jun 2021 | 12 |
| Jul 2021 | Oct 2020 to Jul 2021 | 10 |
| Aug 2021 | Oct 2020 to Aug 2021 | 11 |
| Sep 2021 | Oct 2020 to Sep 2021 | 12 |
| Oct 2021 | Jan 2021 to Oct 2021 | 10 |
| Nov 2021 | Jan 2021 to Nov 2021 | 11 |
| Dec 2021 | Jan 2021 to Dec 2021 | 12 |
the below is just by months and needs to be by quarters with the above table as reference
' lvmain1 date is mmddyyyy ie 01202020
For zzz = LVMain1.ListItems.Count To 1 Step -1
DDate = LVMain1.ListItems(zzz).SubItems(10)
CDDate = Left(DDate, 2) & "-" & Mid(DDate, 3, 2) & "-" & Right(DDate, 4)
If (Year(Now) * 12 + Month(Now)) - (Year(CDDate) * 12 + Month(CDDate)) > 11 Then
LVMain1.ListItems.Remove (zzz)
End If
Next zzz
Visual Basic Classic
Last Comment
Martin Liss
Can you supply a sample project in a zip file?
ASKER
would this help
please review that spread sheet data and remove rows that the date does not fall into the previous table logic
in vb
resutl.csv
please review that spread sheet data and remove rows that the date does not fall into the previous table logic
in vb
resutl.csv
ASKER
the date to compare it to is NOW
would this helpSorry but no. If the data is sensitive I only need a few obfuscated lines.
ASKER
that is the data
ASKER
let me to put it into a list box just a sec
I'd really like to have your project so that I don't have to create the form, fill the listview, etc,
ASKER
so here is the project but instead of removing rows by months i need to remove them by the quarter in the previous chart
RemoveByMonth.zip
RemoveByMonth.zip
ASKER
i guess another way of saying it is
if the current month is Jan then i need to keep the current month and the previous 10 months including jan
if the current month is feb then i need to keep the last 11 months
if the current month is mar then 12 months
if apr then back to 10 month etc
if the current month is Jan then i need to keep the current month and the previous 10 months including jan
if the current month is feb then i need to keep the last 11 months
if the current month is mar then 12 months
if apr then back to 10 month etc
Please describe the logic for removing an item. For example quarter of today minus quarter of listitem greater that 4 then deleye the item.
ASKER
sorry i don't understand
do you understand the below?
it is rolling from keep 10 months to keeping 12 months depending on what month of the quarter that is now
i guess another way of saying it is
if the current month is Jan then i need to keep the current month and the previous 10 months including Jan
if the current month is feb then i need to keep the last 11 months
if the current month is mar then 12 months
if apr then back to 10 month etc
do you understand the below?
it is rolling from keep 10 months to keeping 12 months depending on what month of the quarter that is now
i guess another way of saying it is
if the current month is Jan then i need to keep the current month and the previous 10 months including Jan
if the current month is feb then i need to keep the last 11 months
if the current month is mar then 12 months
if apr then back to 10 month etc
Yes I do; we cross-posted.
ASKER
ok cool sorry
if the current month is feb then i need to keep the last 11 monthsDoes that mean you want to keep
Feb 2021
Jan 2021
Dec 2020
Nov 2020
Oct 2020
Sep 2020
Aug 2020
Jul 2020
Jun 2020
May 2020
Apr 2020
and delete the rest?
Here is a preliminary project. In it, to enable repeat testing, I replaced the line that would actually delete the listbox entry with lstKept and lstDeleted sorted listboxes. I also temporarily replaced Now with dteTest, again to aid testing. So far I only tested February. Let me know if the results of that are correct.
Also IMO you should always use Option Explicit and Dim all your variables, giving them meaningful names.
29216573.zip
Also IMO you should always use Option Explicit and Dim all your variables, giving them meaningful names.
29216573.zip
ASKER
sorry they need to stay in the order they are in the list box.
ASKER
does it need to be sorted?
The two listboxes I added are temporary and only meant to be used to help with testing. lstDeleted should contain all the dates that should be deleted and lstKept should contain all the dates that should be kept. I just need you to tell me if the contents of those listboxes (regardless of order) is correct.
ASKER
shouldn't these cases have code under them
Case 2, 5, 8, 11
Case 3, 6, 9, 12
Case 2, 5, 8, 11
Case 3, 6, 9, 12
Yes, but first I need to find out if the first case is correct. Is it?
ASKER
how do i change this
dteTest = "02-01-2021"
to NOW?
dteTest = "02-01-2021"
to NOW?
Change dteTest = "02-01-2021" to dteTest = Now, but please answer my question.
ASKER
i am trying to test will let you know?
sorry it may take a while
sorry it may take a while
I believe all you need to do is to look in lstDeleted and see if there are any dates that should not be there, and then look in lstKept and see if there are any dates that should not be there. Both lists are sorted by date so it should be easy. If both are correct that's good but if not please tell me what month(s) are showing up in the wrong list.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
i think we are good. i added the other cases . thanks for the help
You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Distinguished Expert in Excel 2018
Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
Experts Exchange Top Expert VBA 2018 to 2020
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Distinguished Expert in Excel 2018
Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
Experts Exchange Top Expert VBA 2018 to 2020