Solved Date format filter (1 Viewer)

suryu

Member
Local time
Today, 20:01
Joined
Apr 3, 2020
Messages
86
Hi, i want to filter column having date format in "dd-mmm-yy"
Column range :=E1 and autofilter field:=5
 

June7

AWF VIP
Local time
Today, 06:31
Joined
Mar 9, 2014
Messages
5,423
Code does not work? What happens?

What is actually in the cell? Text or a formatted number?
 

Isaac

Lifelong Learner
Local time
Today, 07:31
Joined
Mar 14, 2017
Messages
8,738
Are you talking about Excel VBA?
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:31
Joined
Sep 21, 2011
Messages
14,044
Hi, i want to filter column having date format in "dd-mmm-yy"
Column range :=E1 and autofilter field:=5
Use the Macro Recorder to get the basics, then amend to suit.
 

suryu

Member
Local time
Today, 20:01
Joined
Apr 3, 2020
Messages
86
Code does not work? What happens?

What is actually in the cell? Text or a formatted number?
Its formatted number

i am trying below code which is not working

(My column name is closing date)

strDate="06-Aug-10"

With Activesheet
.AutoFilterMode= False
.Range("E1").autoFilter
.Range("E1").AutoFilter Field:=5, Citeria1:=">" & Format(strDate, "dd-mmm-yy")

End With
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:31
Joined
Sep 21, 2011
Messages
14,044
Format is just that, a format.
Search for the date as a normal date.

Macro recorder would show you that.?
 

June7

AWF VIP
Local time
Today, 06:31
Joined
Mar 9, 2014
Messages
5,423
Misspelling: Citeria1 should be Criteria1. Code works. Don't even need Format() function.
 

suryu

Member
Local time
Today, 20:01
Joined
Apr 3, 2020
Messages
86
Misspelling: Citeria1 should be Criteria1. Code works. Don't even need Format() function.
Criteria1 is fine but still filter is not working, in column blank is there but what i want date value to be filtered.
 
Last edited:

missinglinq

AWF VIP
Local time
Today, 10:31
Joined
Jun 20, 2003
Messages
6,423
Why are you posting a question about Excel VBA on an ACCESS forum?

All VBA is not created equal!

You should probably try a forum dedicated to MS Excel.

Linq ;0)>
 

Minty

AWF VIP
Local time
Today, 14:31
Joined
Jul 26, 2013
Messages
10,354
Your date field is stored as a number in excel as it would be in Access.
You need to filter by a date value not a string.

> DateValue (strDate)
 

Isaac

Lifelong Learner
Local time
Today, 07:31
Joined
Mar 14, 2017
Messages
8,738
As Gasman suggested, this is a time to use the macro recorder and then adjust the resulting code to suit.
 

Micron

AWF VIP
Local time
Today, 10:31
Joined
Oct 20, 2018
Messages
3,476
Why are you posting a question about Excel VBA on an ACCESS forum?
There is an Excel forum here? The question belongs there if that's what the issue is about, but it's not uncommon to post things in the wrong place, no?
 

June7

AWF VIP
Local time
Today, 06:31
Joined
Mar 9, 2014
Messages
5,423
I tested your code and it works for me - with or without Format() function. If you want to provide file for analysis, follow instructions at bottom of my post.
 

suryu

Member
Local time
Today, 20:01
Joined
Apr 3, 2020
Messages
86
Why are you posting a question about Excel VBA on an ACCESS forum?

All VBA is not created equal!

You should probably try a forum dedicated to MS Excel.

Linq ;0)>
Ok thanks it was not known to me
 

Users who are viewing this thread

Top Bottom