Sql Filter between two dates - ADO

[Rick Lipkin]

To All

I have a Access sql table that has two datetime fields HDATE and LDATE and I have a button where when I click the button I can set a filter between the two dates

Ldate := ctod("01/01/2023")
Hdate := ctod("01/31/2023")

oRs:Filter := ""
oRsFilter := oRs:Fields("Ldate"):Value >= lDate .and. oRs:Fields("Hdate"):Value <= hDate

I have not tried this code but I don't think it will work ?? any ideas ??

Rick Lipkin

[nageswaragunupudi]

If you are working with MSACCESS

Code: Select all  Expand view  RUN

oRs:Filter := "LDATE >= #2023-01-01# AND HDATE < #2023-02-01#"

If you are working with Microsoft SQL

Code: Select all  Expand view  RUN

oRs:Filter := "LDATE >= '2023-01-01' AND HDATE < '2023-02-01'"

Note: It is safer to use "HDATE < (date + 1 )" than "HDATE <= (date)", because many RDBMS use datetime fields.

How to easily generate these expressions?
We better make it a habit to open an ADO connection using FW_OpenAdoConnection() of FWH instead of opening on our own with ADODB.Connection.
If we open using FWH function FW_OpenAdoConnection, all the other ADO functions know the RDMS used and function in a say appropriate to the RDMBS.

Please try this:

Code: Select all  Expand view  RUN

  lDate    := STOD( "20230101" )
   hDate    := STOD( "20230131" )
   oCn   := FW_OpenAdoConnection( "xbrtest.mdb" )
   ? C := FW_AdoApplyParams( "LDATE >= ? AND HDATE < ?", { lDate, hDate + 1 } )
   oCn:Close()

   oCn   := FW_MSSQLDB()
   ? C := FW_AdoApplyParams( "LDATE >= ? AND HDATE < ?", { lDate, hDate + 1 } )
 

[Rick Lipkin]

Rao

Here was my solution .. been so long since I have worked in MS Access ..... I forgot about having to use the the hash tag .. here is my working solution...

Thanks
Rick Lipkin

Code: Select all  Expand view  RUN

oRsTrips:Filter := ""
oRsTrips:Filter := "[PICKUPDATE] >= #"+dtoc(lDate)+"# and [PICKUPDATE] <= #"+dtoc(hDate)+"#"

Try
   oRsTrips:MoveFirst()
Catch
End Try

oLbxA:MakeTotals()
oLbxA:ReFresh()

SysReFresh()

If oRsTrips:Eof
   Saying := "Sorry .. there were no records found for dates "
   Saying += dtoc(lDate)+"  thru  "+dtoc(hDate)

   Msginfo( Saying )

   oButt5:CLick()   // view all
Endif

Return(nil)

 

[Armando]

Tim and friends:

Here is an other way, using the BETWEEN clause.

Code: Select all  Expand view  RUN

                oRsPag:Filter := "pro_nom LIKE " + "'%" + cFilNom + "%'" +;
                                            "AND " +;
                                                "det_fdp BETWEEN '" + DToS(dFecIni) + " AND " + DToS(dFecFin) + "' " +;
                                            "AND " +;
                                        "pro_nom LIKE " + "'%" + cFilNom + "%'"
 

Best regards