Solved

Filter old expiration dates

Posted on 2011-03-22
45
230 Views
Last Modified: 2012-05-11
Dear experts,

I want to filter old expiration dates on my sheet and from my user form.

user form actually contains 4 fields:
Acctopid
Gslacnum
Proj name
Policy exp date

What's complex is to show policy exp dates, meaning when they are already expired and it's from column AY (PLCYXPDT).

Thanks in advance for your help.
0
Comment
Question by:currentdb
  • 25
  • 20
45 Comments
 
LVL 1

Author Comment

by:currentdb
ID: 35190167
There's a sample with a button on sheet 3 (Filter plcy expiration).

If data can be shown on sheet Query_FOR_GSLS2, we need only to see these fields:
ACCTOPID
GSLACNUM
PROJECT_NAME
PLCYXPDT

Once you enter the Acctopid number (from column O), it should show these fields.



userform-sample-v7b.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190169
Which user form are we referring to?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190186
Sorry, I was referring to the old file. Let me go through the above file.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190189
I just posted the sample file, created userform 5, had no time yet to incorpore the changes from what we did in the other question, but will do it later.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190240
So for example

If ACCTOPID is 13 then you want to show only Row 3?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190263
No worries.

I forgot to add these dates to the sheet Query for QSL2 in the  column AY if you need to test with any dates

2011-03-20
2011-02-09

You can type in any date.

The real sheet contains multiple dates from this year, the previous year and as well the next year (2012).

But for this, only show expiration dates following the ''Acctopid'' number in column O. And expiration dates can be as close to the actual date (date now) and before.

I was planning to use the UserForm 5 I created, because upon filtering these expiration dates, the other columns does not need to be seen or visible, only the 4 fields, just as it is on UserForm 5.

If that's too complex, maybe we can use the userform 1..the old one we had before and show the 4 relevant fields there and of course, print them from there (if possible).
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190265
or do you want to specify the Expiry date using TextBox4?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190278
We both posted at the same time. Let me read your last post :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190327
LOL I know. Just wanted to explain more how it will work :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190393
>>>>And expiration dates can be as close to the actual date (date now) and before.

So correct me if I am wrong.

For ACCTOPID say 13, you want to display all dates which are less than TextBox4?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190460
For ACCTOPID say 13, you want to display all dates which are less than TextBox4? --> Yes, meaning if today we are on March 22, it will show expiration dates (if any of course) for this day and previous days,months, years.

If the actual worksheet is opened let's say this Friday and if there are other expiration dates, then these can be filtered as well. You can't show dates that will expire soon even if it's as soon as tomorror or later this week. But if you open this worksheet every day, it will look for expiration dates from previous years, months, days until now.

0
 
LVL 1

Author Comment

by:currentdb
ID: 35190478
Do you think UserForm1 would be a good asset ? Cause if entering acctopid id number, it will show the relevant fields down...something which we don't have on UserForm 5.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190479
Got it. :) I am making slight change to your userform.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190513
No problem take your time and let me know if you need more info.

Just made a quick check and Acctopid id numbers are from 1 to 13.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190593
I am almost done. The only challenge that I see is that the dates are stored as text in Col AY.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190653
Ahhhh, you can change these back to numbers. As the data is refreshed every day from a SAS database, it comes back to text.

The only idea I have to make it disappear for good will be to link the sheet to an Access database who is refreshed at the same time. If I do this, then col AY will appear as number.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35190853
Ok I am assuming col AY will appear as valid date.

Try this file and let me know if this is OK. I haven't tested it yet.

Sid
userform-sample-v7b-1.xlsm
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190906
Ok I'm on it.
0
 
LVL 1

Author Comment

by:currentdb
ID: 35190939
Sid, I don't see the changes made to the userform. The file is basically the one I send you before and it does not include the changes you made. Maybe you did not include the right one :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191092
>>>Maybe you did not include the right one :)

Are you sure? I downloaded the above file and I can see the changes that I made to userform5 :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191150
Ok hold on...I'm missing something. I hope my computer is not playing devil games :)
I'm gonna check it again. Will be back in the following minutes if I survive of course :)
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191224
The button ''Filter policy expiration'' was not programmed correctly. I'm trying to re-programm it, but ended in making the other 2 buttons useless. Now trying to put everything back on :(
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191336
Ok. I didn't touch them. I directly worked on the userform.

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191351
I'm back and the button is now programmed to show userform 5 correctly.

Made a test and entered this info:
Acctopid: 13
Proj name: LIVELY EDWARDM.
Expy date: 25/02/2011

But it does not show anything on the sheet Query for Gsl 2

I think it's better to remove Gslacnum, Proj name, Plcyxpdt from UserForm 5 and just keep Acctopid (just as it was in the previous question).

Now if entering exemple Acctopid 13, pushing the button ''filter'', it would show the relevant data (Acctopid, Gslacnum, Proj name, Plcyxpdt)

I hope it's not too complex.

I re-added the same sample, with the button now working.
userform-sample-v7b-1.xlsm
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191510
It was my fault because I forgot to program this button :)

If you missed my post, it's on ID 35191351 :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191526
Ok I tested it and it works fine now. Just change the line. Let other things remain as it is.

.AutoFilter Field:=51, Criteria1:="<" & Clng(Dt)

to

.AutoFilter Field:=51, Criteria1:="<" & Dt

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191543
Here is the updated file.

Sid
userform-sample-v7b-1.xlsm
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191551
BTW I tested with this data

13
1220490
LIVELY EDWARDM.
25/02/2011

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191640
Just tested it and it works :)

But what if you have multiple records associated to the acctopid number ?

If we remove the fields Gslacnum, Proj_name, Plcyexpdt from UserForm5, would it work ?

By exemple, say you enter acctopid number 13 on UserForm5 (no need to use the other fields on UserForm5) and on the sheet Query for Qsl 2, there are 4 records associated with this number. I made some changes to the PLCYXPDT field as you can see on attached file. Only one record will expire later this year in April which means it will not be showed within the actual filtering.
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191673
I forgot to re-attach the sample
userform-sample-v7b-2.xlsm
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35191746
>>>Only one record will expire later this year in April which means it will not be showed within the actual filtering.

Yes that is because it is greater than 25/02/2011 :)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191780
Yes that is because it is greater than 25/02/2011 :) ---> That's good news!

On the other side, would it work if we remove the fields Gslacnum, Proj_name and Plcyxpdt from Userform 5 ? (we will only keep the Acctopid field and of course the ''Filter'' button).

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191793
Yes you can do that as well but that will filter only by Acctopid field.

Is that what you want?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191811
Yes you can do that as well but that will filter only by Acctopid field. --> Yes (and hope it's not too complex)

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191828
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191884
Yes like this, but as I entered acctopid 13, all 4 records were there and that includes the one with a date greater than today (the one that expires in April). I'm puzzled.

If I change the date for let's say tomorrow March 23, it will still show on the sheet.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35191891
Ok you don't want to show dates greater than today correct?

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191911
Ok you don't want to show dates greater than today correct? --> Yes exactly. Only show older dates (including today date if any) from actual date and back. Future expiration dates does not need to be shown.

Of course, if I re-open the worksheet tomorrow, I'll see the record that expires on 23 for sure...but we are not yet on March 23 :)

0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 500 total points
ID: 35191921
Is this what you want?

Sid
userform-sample-v7b-1.xlsm
0
 
LVL 1

Author Comment

by:currentdb
ID: 35191973
Youppiii! Yes it's exactly that :)

One last request, but feel free to ask me if you want me to open a different question.

Some records does not include any expiration date. For this, a second button next to the ''filter'' button would be preferable like ''filter blackout dates'' and only show fields that does not have any expiration date.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35192026
Different question needs different post ;)

Sid
0
 
LVL 1

Author Comment

by:currentdb
ID: 35192034
Sure no problem, I'll open a different question and post the link back :)
0
 
LVL 1

Author Comment

by:currentdb
ID: 35192078
0
 
LVL 1

Author Closing Comment

by:currentdb
ID: 35192130
Thanks very very very much for your help!
0
 
LVL 1

Author Comment

by:currentdb
ID: 35198968
Hello Sid,
Just posted a new question here:http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26906133.html

It's an easy one...but I'm puzzled that it does not work :(
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now