Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Filter old expiration dates

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
currentdb
Asked:
currentdb
  • 25
  • 20
2 Solutions
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Which user form are we referring to?

Sid
0
 
SiddharthRoutCommented:
Sorry, I was referring to the old file. Let me go through the above file.

Sid
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
So for example

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

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
or do you want to specify the Expiry date using TextBox4?

Sid
0
 
SiddharthRoutCommented:
We both posted at the same time. Let me read your last post :)

Sid
0
 
currentdbAuthor Commented:
LOL I know. Just wanted to explain more how it will work :)
0
 
SiddharthRoutCommented:
>>>>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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Got it. :) I am making slight change to your userform.

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
I am almost done. The only challenge that I see is that the dates are stored as text in Col AY.

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
currentdbAuthor Commented:
Ok I'm on it.
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
>>>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
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Ok. I didn't touch them. I directly worked on the userform.

Sid
0
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
It was my fault because I forgot to program this button :)

If you missed my post, it's on ID 35191351 :)
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
Here is the updated file.

Sid
userform-sample-v7b-1.xlsm
0
 
SiddharthRoutCommented:
BTW I tested with this data

13
1220490
LIVELY EDWARDM.
25/02/2011

Sid
0
 
currentdbAuthor Commented:
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
 
currentdbAuthor Commented:
I forgot to re-attach the sample
userform-sample-v7b-2.xlsm
0
 
SiddharthRoutCommented:
>>>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
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Yes you can do that as well but that will filter only by Acctopid field.

Is that what you want?

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

0
 
SiddharthRoutCommented:
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Ok you don't want to show dates greater than today correct?

Sid
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Is this what you want?

Sid
userform-sample-v7b-1.xlsm
0
 
currentdbAuthor Commented:
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
 
SiddharthRoutCommented:
Different question needs different post ;)

Sid
0
 
currentdbAuthor Commented:
Sure no problem, I'll open a different question and post the link back :)
0
 
currentdbAuthor Commented:
0
 
currentdbAuthor Commented:
Thanks very very very much for your help!
0
 
currentdbAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 25
  • 20
Tackle projects and never again get stuck behind a technical roadblock.
Join Now