Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to filter a database displays no rows at all
Good afternoon!
I recorded a macro to filter a database to show records where the entry in column 1 was between two dates thus: Sub DateFilterOn() Range("MainDatabaseStart").AutoFilter Field:=1, _ Criteria1:="=23/10/2007", Operator:=xlAnd, _ Criteria2:="<=22/11/2007" End Sub When I run the code, it filters the database down and displays no rows at all (which is incorrect, as there are loads of them that meet the criteria). If I then go into Data Filter Custom manually on the first DB column, the criteria from the code appear in the dialog box and when I click "OK", the database is correctly filtered. Can anyone tell me where I'm going wrong, please? Why won't my code filter my data correctly without the manual intervention? Thanks in advance. pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to filter a database displays no rows at all
Hi Pete
Use the US date format mm/dd/yyyy instead of the DMY you use now in the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete Rooney" wrote in message ... Good afternoon! I recorded a macro to filter a database to show records where the entry in column 1 was between two dates thus: Sub DateFilterOn() Range("MainDatabaseStart").AutoFilter Field:=1, _ Criteria1:="=23/10/2007", Operator:=xlAnd, _ Criteria2:="<=22/11/2007" End Sub When I run the code, it filters the database down and displays no rows at all (which is incorrect, as there are loads of them that meet the criteria). If I then go into Data Filter Custom manually on the first DB column, the criteria from the code appear in the dialog box and when I click "OK", the database is correctly filtered. Can anyone tell me where I'm going wrong, please? Why won't my code filter my data correctly without the manual intervention? Thanks in advance. pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to filter a database displays no rows at all
Ron,
Nice to hear from you again! As usual, spot on answer :-) Thank you! Pete "Ron de Bruin" wrote: Hi Pete Use the US date format mm/dd/yyyy instead of the DMY you use now in the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Pete Rooney" wrote in message ... Good afternoon! I recorded a macro to filter a database to show records where the entry in column 1 was between two dates thus: Sub DateFilterOn() Range("MainDatabaseStart").AutoFilter Field:=1, _ Criteria1:="=23/10/2007", Operator:=xlAnd, _ Criteria2:="<=22/11/2007" End Sub When I run the code, it filters the database down and displays no rows at all (which is incorrect, as there are loads of them that meet the criteria). If I then go into Data Filter Custom manually on the first DB column, the criteria from the code appear in the dialog box and when I click "OK", the database is correctly filtered. Can anyone tell me where I'm going wrong, please? Why won't my code filter my data correctly without the manual intervention? Thanks in advance. pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: VBA Code to extract data from an SQL Server database and put it into Access Database | Excel Programming | |||
Worksheet change code to colour in rows in a database | Excel Programming | |||
Custom Filter that Displays All But in Ascending or Decending Orde | Excel Discussion (Misc queries) | |||
Clicking on listbox control displays code - why? | Excel Programming | |||
Hidden rows Database Filter | Excel Programming |