+ Reply to Thread
Results 1 to 17 of 17

Filter data based on two dates using ADODB

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Filter data based on two dates using ADODB

    Hi All,

    I have written code to select data between two dates.

    But unfortunately it is not working when I select 1st date from May and 2nd date from June. If I give both the dates from May, it works.

    Please find the attached Macro file and have a look.

    Macro is activated on the click of the button in sheet called "Macro"

    Thanks in advance.

    Regards,
    Tejas
    Attached Files Attached Files
    Last edited by Tejas.T; 12-10-2012 at 09:52 AM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Filter data based on two dates using ADODB

    Works for me. Whats not working?
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Filter data based on two dates using ADODB

    Hi -

    Works for me too.

    Regards,
    Event

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Filter data based on two dates using ADODB

    More importantly, you shouldn't be querying a workbook like that. It causes a memory leak - if you want to use sql on a workbook, the workbook should not be the one in which the code resides - you need to create a workbook to do the querying and have one workbook with the data in

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data based on two dates using ADODB

    @ mike & event.

    Does not work for me too. Row 8 of the first sheet is not copied in the new sheet.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Filter data based on two dates using ADODB

    Hi,
    If you select 25-May-2012 as Date1 and 2-Jun-2012 as Date2, how many rows you are getting as result?
    I am getting only 4 rows and it is not fetching row with 01-06-2012 as ArrivalDate.

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Filter data based on two dates using ADODB

    The issue is your dates:

    Please Login or Register  to view this content.
    SQL dates should always be passed as yyyy-mm-dd, try:

    Please Login or Register  to view this content.
    But as I mentioned above, you shouldn't query a workbook like this

  8. #8
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Filter data based on two dates using ADODB

    WoW. It worked!!

    Thanks a lot.

    Well I have no other option. Client has instructed me to create a single file has deliverable.
    I can not give him two files, one as Data File and another as Macro File.

    Please suggest me if you have a better work-around for the same.

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Filter data based on two dates using ADODB

    You can try this

    Please Login or Register  to view this content.

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Filter data based on two dates using ADODB

    @Fortis1991

    Ah, you are correct. I'm just on my first cup of coffee and not fully awake. Nice catch.

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data based on two dates using ADODB

    ...@Fortis1991

    Ah, you are correct. I'm just on my first cup of coffee and not fully awake. Nice catch.
    I can see this Mike! Fotis ..not Fortis

    I have 2 questions here.

    1) Code is not working for me. Am i doing something wrong?

    2) What if we need one more condition. lET'S say that in macro sheet in C1, put another criterion. Let's say number 391.

    So result must be data with dates>=Macro!B1 AND <=Macro!B2 and =Macro!C1

    So the two yellows rows in first sheet must be the result.
    Attached Files Attached Files

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Filter data based on two dates using ADODB

    @Tejas, cross-posting is against the rules, if you have solved your problem, please post this wherever you have asked the question. It saves other people spending time helping yuo when you already have a solution

  13. #13
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Filter data based on two dates using ADODB

    @Fotis1991
    I can see this Mike! Fotis ..not Fortis
    Must be my Fat fingers... LOL This would include the ID filter. See attached wb.



    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data based on two dates using ADODB

    Must be something wrong in my PC. Same result!

    Never mind. Just was interesting for me and asked. Don't do effort for this.

  15. #15
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Filter data based on two dates using ADODB

    Just a quick suggestion try changing

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  16. #16
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data based on two dates using ADODB

    Neither this!

  17. #17
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Filter data based on two dates using ADODB

    @Kyle123

    Sorry I posted it again because someone deleted my post by mistake. I got the message from Forum Admin and after that I posted it once again.

    BTW I am not able to count the number of rows returned.
    Can you please help me out how can I get the number of records returned by the query?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1