Announcement

Collapse
No announcement yet.

SQL Server 2012, ADO, Recordset.Filter

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Server 2012, ADO, Recordset.Filter

    I am having trouble getting the ADO Recordset.Filter to work with dates. This is on SQL Server 2012.
    I have a database of employees using a card reader to access a building. I can return a recordset using the following:
    Code:
      SqlStr = "SELECT [CardNumber],[EventDate],[EventLocation] " & _
               "FROM [MYDB].[dbo].[vwEvents] " & _
               "WHERE ([EventType]=601 OR [EventType]=301) " & _
                      "AND YEAR(EventDate)=2020 AND MONTH(EventDate)=09 AND DAY(EventDate)=30 " & _
                      "AND [CardNumber]<>0 " & _
               "ORDER BY [CardNumber],[EventDate]"
    and filter it by [CardNumber]
    Code:
    pRecordset.Filter = "CardNumber=1272839"
    Now if I pull the date filter from the select statement like this:
    Code:
      SqlStr = "SELECT [CardNumber],[EventDate],[EventLocation] " & _
               "FROM [MYDB].[dbo].[vwEvents] " & _
               "WHERE ([EventType]=601 OR [EventType]=301) " & _
                      "AND [CardNumber]<>0 " & _
               "ORDER BY [CardNumber],[EventDate]"
    and add a filter like this:
    Code:
    pRecordset.Filter = "YEAR(EventDate)=2020 AND MONTH(EventDate)=09 AND DAY(EventDate)=30"
    I get this error:
    Arguments are of the wrong type, Are out of acceptable range or are in conflict with one another.
    I tried making the Filter shorter:
    Code:
    pRecordset.Filter = "YEAR(EventDate)=2020"
    and got the same error. I even tried:
    Code:
    pRecordset.Filter = "DATEPART(year, EventDate)=2020"
    last attempt was this:
    Code:
    pRecordset.Filter = "EventDate='#09/30/2020#'"
    It gave me a type mismatch. I think I needed (2) single quotes like this:
    Code:
    pRecordset.Filter = "EventDate=''#09/30/2020#''"
    Which got me back to the original wrong type error.

    So how do I use a filter on a date field?

  • #2
    I suspect it has something to do with the time as well. If I use:
    Code:
    pRecordset.Filter = "Eventdate>#9/30/2020# AND Eventdate<#10/01/2020#"
    It shows just the events for 9/30/2020. Off to do more reading.

    Comment


    • #3
      Well to get it to work I had to create a field in the recordset with just dates and no times.

      FilterDate is the new field:
      Code:
        SqlStr = "SELECT [CardNumber],[EventDate],[EventLocation], " & _
                         "CONVERT(varchar, EventDate, 112) AS FilterDate " & _
                 "FROM [MYDB].[dbo].[vwEvents] " & _
                 "WHERE ([EventType]=601 OR [EventType]=301) " & _
                        "AND [CardNumber]<>0 " & _
                 "ORDER BY [CardNumber],[EventDate]"
      Then I could use the filter:
      Code:
      pRecordset.Filter = "FilterDate='20200930'"

      Comment


      • #4
        Looks like the error stems from two things:

        1. calling a DateTime or DateTime2 field "EventDate" and making a wrong assumption about the content of the field
        2. Mixing SQL functions and ADO functions and using the wrong one at the wrong time. The ADO Recordset's Filter property does not use the target DBMS's SQL
        syntax.

        e.g.

        DATEPART(year, EventDate)=2020" - Working with an ADO dataset,: DATEPART('yyyy"....


        pRecordset.Filter = "EventDate='#09/30/2020#'" shouldn't have the single quotes
        But pRecordset.Filter = "EventDate=#09/30/2020#" will still fail unless the time part is 0.




        Comment


        • #5
          I suggest replacing handcrafted SQL queries with Parameterized Queries: https://docs.microsoft.com/en-us/sql...l-server-ver15

          It provides two benefits: value conversion and sanitize input value. The latter helps to secure your application from attacks like SQL injections

          Comment


          • #6
            SInce you are using a view (FROM [MYDB].[dbo].[vwEvents]), you could make filtering, sorting and grouping easier by designing it so that the event date (without time), year, month and day are separate fields in the view

            Comment


            • #7
              Knuth: that was what I was going to look into next. What I have works for '=' but it does not work with '<' or '>'.
              Stuart: the timeclock software is from an outside source, can't be changed.

              Comment


              • #8
                I take that back. '<' and '>' work. Not sure what I was looking at. The following gives me all the records from last month for a single employee.
                Code:
                pRecordset.Filter = "FilterDate > '20200900' AND FilterDate < '20201000' AND CardNumber=1272822"

                Comment


                • #9
                  Originally posted by Frank Rogers View Post
                  Stuart: the timeclock software is from an outside source, can't be changed.
                  The timeclock software may be from an oujtside source, but it's using SQL Server. You have access to the database so you should be able to create your own view.

                  Comment


                  • #10
                    that was what I was going to look into next. What I have works for '=' but it does not work with '<' or '>'.
                    I think MS SQL/Server supports (I know Oracle does) the "BETWEEN" SQL operator which might work better than using ">= startdate AND <=endDate" .

                    e.g.
                    Code:
                    Select X
                        from Y
                        where fdate
                               BETWEEN startdate and Enddate
                    Either in a VIEW or SELECT it would eliminate the need for the compound condition.. and maybe even the need for an ADO filter. .

                    Michael Mattias
                    Tal Systems Inc. (retired)
                    Racine WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment


                    • #11
                      A couple of ideas for you...

                      I.
                      I looked up the recordset.filter property in my ADO reference and found,, "Dates are surrounded by pound signs (#) (#06/20/1973#) " and AND or OR are supported.

                      Maybe you should try that if working with literals.

                      II.
                      Another thing which might be messing you up is, I think Microsoft products (e.g., Access and SQL/Server) use the variant date for its date storage. There are functions to convert to/from Variant date, sometimes called "VBDate", I think provided by PB in one or more of the INCLUDEs. The functions are named something like "VBDateToSystemTime" and the reverse SystemTimetoVBDate or something like that.

                      I know I have used them but damned if remember where I put them.

                      Heck let me look one more place.. Gold has been struck!

                      Found the DECLAREs in the PB-supplied "OLEAUTO.INC.... here's a starter example
                      Code:
                      DECLARE FUNCTION SystemTimeToVariantTime LIB "OleAut32.dll" _
                           ALIAS "SystemTimeToVariantTime" ( _
                          lpSystemTime AS SYSTEMTIME, _
                          pvtime AS DOUBLE _
                      ) AS LONG
                      
                      DECLARE FUNCTION VariantTimeToSystemTime LIB "OleAut32.dll" _
                           ALIAS "VariantTimeToSystemTime" ( _
                           BYVAL vtime AS DOUBLE, _
                          lpSystemTime AS SYSTEMTIME _
                          ) AS LONG
                      Maybe those can help you. (Column description of your "eventdate" column not shown so I am just throwing out everything I can find)
                      Michael Mattias
                      Tal Systems Inc. (retired)
                      Racine WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        Hi Michael,

                        I tried "BETWEEN"
                        Code:
                        pRecordset.Filter = "EventDate BETWEEN #9/01/2020# AND #9/30/2020#"
                        but it came back with the same error as before being the wrong type or out of range

                        I tried ">"
                        Code:
                        pRecordset.Filter = "EventDate > #9/01/2020#"
                        and that worked. Not sure why it didn't before.

                        I am using Jose' CAfxTime for variant conversion
                        Code:
                        LOCAL T AS IAfxTime: T = CLASS "CAfxTime"
                        T.FromVarDate(vItem)
                        T.DateFormat("yyyyMMdd")
                        T.TimeFormat("HHmmss")
                        Thank you for making me try it again.
                        Frank

                        Comment


                        • #13
                          Ahhh, just figured it out... '#10/01/2020#' does not work but #10/01/2020# does work. The single quotes mess up the filter.
                          Stuart had that in post #4 and I missed it.

                          Comment


                          • #14
                            FWIW, I believer "DATEPART()" is a Microsoft-specific SQL 'extension' and your code may fail on other DBs.

                            Does not appear to be in the final use code here , but it IS something to watch out for.

                            Both Microsoft and Oracle databases (and I am sure others!) support a number of functions with their SQL which are proprietary.

                            Since one of the reasons for using ADO is to become DB brand agnostic, you have to take care to use only 'standard' SQL or your 'brand-agnostic' attempt fails.

                            Just a little something to keep in mind!

                            MCM
                            Michael Mattias
                            Tal Systems Inc. (retired)
                            Racine WI USA
                            [email protected]
                            http://www.talsystems.com

                            Comment


                            • #15
                              Originally posted by ]=Michael Mattias View Post
                              FWIW, I believer "DATEPART()" is a Microsoft-specific SQL 'extension' and your code may fail on other DBs.
                              It's not being used with SQL, it's being applied to the resultant ADO recordset, so it should work with any DBMS

                              Since one of the reasons for using ADO is to become DB brand agnostic, you have to take care to use only 'standard' SQL or your 'brand-agnostic' attempt fails.
                              Quite the opposite. The main reason for using ADO is so that once you have a recordset, you do NOT have to use only "standard" SQL.

                              That's the whole point of pRecordset.Filter. The filter uses ADO constructs, not "standard SQL"..

                              Comment

                              Working...
                              X