Correct Date syntax for cell and textbox find

[coreytroy]

I am trying to get the correct syntax to enable a search criteria, and populate a listbox.
The below code does not work 100% of the time as expected.

I tried using

Please Login or Register  to view this content.

instead of the following but i only ever get the same inconsistant result.

The .Cells dates are in a dddd dd mmmm yyyy format, and the textbox format is dd/mm/yy.

The start date is textbox1, and the end date is textbox2, set manually by the user.

What is the correct syntax to obtain a correct result of dates between the 2 set ranges of dates?

Please Login or Register  to view this content.

Also, is a simple '>=" & "<=" syntax the correct way to include a date that is equal to the set date?

[coreytroy]

Self solved with:

Please Login or Register  to view this content.

[coreytroy]

Ok, looks like i have jumper the gun with the solving of this.

I seem to be having trouble collecting the dates and having them compared to the textbox dates entered.
Basically on the worksheet i have dates in column C as "dddd dd mmmm yyyy" - EG. Sunday, 2 December 2012.
Yet i have the user enter a date in the start and end date range as "dd/mm/yy".

So the VB object is to compare the date entered into Textbox1(Start Date) to make sure the worksheet date in Column C is greater than it.
Then compare the date entered into Textbox2(End Date) to ensure the worksheet date in column C is less than it.
When the search is run, all values that dates fall into those ranges of dates, have the value offset(-1)EG. Column B in the same row to the date placed into Listbox2.

I have attahced a sample file, with coded set start and end dates.

Use the LOAD button to set the code to diaplay ONLY those values within the date range.
But as will be displayed, ALL values remain, where a few of them should not be added to the list as they are outside the date range set.

[coreytroy]

No takers as to why the start and end dates do not work?

Also is there a way to remove the horizontal scroll bar that turns up on the Listbox. Not sure why it appears, when the value is only a single character.

[JapanDave]

Not exactly sure what you are trying to do?

[Norie]

You shouldn't use Format as that returns text.

I'm not even sure you should use Cdate on the value from the worksheet as it's already a date.

You will need it with the textbox though.

Can you post some dates and what you would expect the results to be?

[coreytroy]

Below is a list of the data in the worksheet:
ID NAME DATE ITEMS
5 FRED Tuesday, 11 September 2012 ORANGE
4 JACK Wednesday, 4 July 2012 ROCKMELON
3 HENRY Sunday, 22 April 2012 PEARS
2 JACK Thursday, 15 March 2012 APPLES
1 FRED Sunday, 1 January 2012 APPLES


When the textboxs (1 & 2) are set(dd/mm/yy) as the start and end dates, ONLY Names should be populated into the Listbox that have a date within those set ranges.

The example file has the start and end date fixed, and just requires the LOAD button clicked to populate ONLY the specified Names, but it still includes ALL names.

I am sure it is a date format problem, as if the day is > 12 is seems to work, otherwise i feel the date reverts to mm/dd/yy instead of dd/mm/yy.

The VBA behind the LOAD button is:

Please Login or Register  to view this content.

[Norie]

It's values you are comparing not formats

All you need is CDate for the textboxes to convert the text date to a 'real' date.

The code below worked for me.

Please Login or Register  to view this content.

[coreytroy]

Thanks for the reply.
I tried to code you posted, and it does norrow down the list, however i get 1 name now (Jack).

If you click on the Listbox value JACK, then Listbox 2 displays 2 ID's. 4 and 2.
Both of which are NOT within the set date ranges??

[Norie]

What dates were you using when you only got Jack?

I've not looked at the other code/controls.

[coreytroy]

I get:
Wednesday, 4 July 2012 & Thursday, 15 March 2012 as displayed

[Norie]

Those are the dates you put in Textbox1 and Textbox2?