Avatar of rjef
rjef
Flag for United States of America 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
 
MonthTime frame to keepMonths
Jan 2021Apr 2020 to Jan 202110
Feb 2021Apr 2020 to Feb 202111
Mar 2021Apr 2020 to Mar 202112
Apr 2021Jul 2020 to Apr 202110
May 2021Jul 2020 to May 202111
Jun 2021Jul 2020 to Jun 202112
Jul 2021Oct 2020 to Jul 202110
Aug 2021Oct 2020 to Aug 202111
Sep 2021Oct 2020 to Sep 202112
Oct 2021Jan 2021 to Oct 202110
Nov 2021Jan 2021 to Nov 202111
Dec 2021Jan 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

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Can you supply a sample project in a zip file?
rjef

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
rjef

ASKER
the date to compare it to is NOW
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Martin Liss

would this help
Sorry but no. If the data is sensitive I only need a few obfuscated lines.
rjef

ASKER
that is the data
rjef

ASKER
let me to put it into a list box just a sec
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

I'd really like to have your project so that I don't have to create the form, fill the listview, etc,
rjef

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
rjef

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Martin Liss

Please describe the logic for removing an item. For example quarter of today minus quarter of listitem greater that 4 then deleye the item.
rjef

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
Martin Liss

Yes I do; we cross-posted.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rjef

ASKER
ok cool sorry
Martin Liss

if the current month is feb then i need to keep the last 11 months
Does 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?
Martin Liss

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
rjef

ASKER
sorry they need to stay in the order they are in the list box.
rjef

ASKER
does it need to be sorted?
Martin Liss

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rjef

ASKER
shouldn't these cases have code under them

        Case 2, 5, 8, 11
        Case 3, 6, 9, 12
Martin Liss

Yes, but first I need to find out if the first case is correct. Is it?
rjef

ASKER
how do i change this
dteTest = "02-01-2021"
to NOW?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Martin Liss

Change dteTest = "02-01-2021" to dteTest = Now, but please answer my question.
rjef

ASKER
i am trying to test will let you know?
sorry it may take a while
Martin Liss

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Martin Liss

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rjef

ASKER
i think we are good.   i added the other cases .  thanks for the help
Martin Liss

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