Hello,

My boss has asked that I create a data entry form for the data entry clerks
to key in a date on my userform. If the customer does not provide us a year
for the birthdate, then we just key in the month and day.

I am using the calendar control. My boss had me set the default year to 1900.

I am using code to query for other fields which work well.

wks.Range("A1:n" & wks.Range("a1").End(xlDown).Row).AdvancedFilter
Action:=xlFilterCopy, CriteriaRange:=wks.Range( _
"t1:an2"), CopyToRange:=wks.Range("ah1:au10000"), Unique:=True

Now if I try to to put in mm/dd in the criteria range cells (z2"), I don't
get anything. Excel automically tries to query for mm/dd/2009.

If I convert mm/dd into a string with 'mm/dd, I still don't get anything.

Can anyone help me understand how to query by mm/dd? If the data reads,

name birthday
Dave 7/1/1900
Mary 7/1/2009
Joe 6/2/2009

If I query for 07/01, I'd like to get the first two rows.

Thanks.

I did some search and playing with the code. I found:

1) Input the dates as strings from the calendar control
2) In the criteria cell (z2), enter ="08/17*"

This pulls up just month and day regardless of the year. Works perfect.



"Webtechie" wrote:

Hello,

My boss has asked that I create a data entry form for the data entry clerks
to key in a date on my userform. If the customer does not provide us a year
for the birthdate, then we just key in the month and day.

I am using the calendar control. My boss had me set the default year to 1900.

I am using code to query for other fields which work well.

wks.Range("A1:n" & wks.Range("a1").End(xlDown).Row).AdvancedFilter
Action:=xlFilterCopy, CriteriaRange:=wks.Range( _
"t1:an2"), CopyToRange:=wks.Range("ah1:au10000"), Unique:=True

Now if I try to to put in mm/dd in the criteria range cells (z2"), I don't
get anything. Excel automically tries to query for mm/dd/2009.

If I convert mm/dd into a string with 'mm/dd, I still don't get anything.

Can anyone help me understand how to query by mm/dd? If the data reads,

name birthday
Dave 7/1/1900
Mary 7/1/2009
Joe 6/2/2009

If I query for 07/01, I'd like to get the first two rows.

Thanks.