Results 1 to 12 of 12

Thread: Display records between two dates

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    3

    Display records between two dates

    How to create a report which shows the records between two specific dates?

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,871

    Re: Display records between two dates

    Can you explain what you use for your report?
    I assume you have to specify the date range in your query.

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Display records between two dates

    Removed sarcasim
    Last edited by TysonLPrice; Oct 12th, 2017 at 11:20 AM.
    Please remember next time...elections matter!

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Display records between two dates

    Hi,

    sample : get data from March
    Code:
    Dim datStart As Date
    Dim datEnde As Date
    Dim lngMonat As Long
    Dim strSQL As String
    
    lngMonat = 3 ' March
    
    If (lngMonat >= 1) And (lngMonat <= 12) Then
        datStart = DateSerial(Year(Date), lngMonat, 1) 
        datEnde = DateAdd("d", -1, DateAdd("m", 1, datStart))
    
        strSQL = "SELECT * FROM Table WHERE (DATUM BETWEEN " & _
                 Format$(datStart, "\#mm\/dd\/yyyy\#") & _
                 " AND " & Format$(datEnde, "\#mm\/dd\/yyyy\#") & _
                 ");"
    
        Debug.Print "strSQL = " & Chr$(34) & strSQL & Chr$(34)
    End If
    or with 2 Dates you want..
    Code:
     Dim d As Date, d1 As Date
       Dim s As String, s1 As String
       Dim sSQL As String
       
          d = CDate("01.10.2017")
          d1 = CDate("15.10.2017")
          
          s = Format(d, "\#mm\/dd\/yyyy\#")
          s1 = Format(d1, "\#mm\/dd\/yyyy\#")
          
          sSQL = "Select * From Table1 Where Datum Between " & s & " And " & s1 & ";"
          Debug.Print sSQL
    regards
    Chris
    Last edited by ChrisE; Oct 13th, 2017 at 12:48 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Display records between two dates

    Don't do what Donny Don't does.

    Use a parameter query, avoid dynamic SQL when possible.

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Display records between two dates

    Quote Originally Posted by dilettante View Post
    Don't do what Donny Don't does.

    Use a parameter query, avoid dynamic SQL when possible.
    why ?

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Display records between two dates

    Quote Originally Posted by ChrisE View Post
    why ?

    regards
    Chris
    http://www.sommarskog.se/dynamic_sql.html#SQL_injection

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Display records between two dates

    Hi Dex,

    thanks for the Link, and yes this true ....
    Before you start to use dynamic SQL all over town, you need to learn about SQL injection and how you protect your application against it. SQL injection is a technique whereby an intruder enters data that causes your application to execute SQL statements you did not intend it to. SQL injection is possible as soon there is dynamic SQL which is handled carelessly...
    I have a Class that handles ..Quotes;Dates;Numbers etc...
    also a Validate of what (and how - a Date for Example) the User is allowed to enter (as any Programmer should take care what the User enters)

    here a sample...
    Code:
    'add or Update a Record
    'cSql is the Class for passing Data
    Public Function RecordAddUpdate() As Long
       Dim sSQL As String, Msg As String
       Dim ID As Long, Records As Long
          ID = Val(txtAdress(0).Text)
          With cSQL
             'neue Feldliste
             .FldValList_New
             'Feldnamen & Feldinhalte generieren
             .FldValList_Add "AD_Name1", .strStr(txtAdress(iAD_Name1).Text)
             .FldValList_Add "AD_Name2", .strStr(txtAdress(iAD_Name2).Text)
             .FldValList_Add "AD_Strasse", .strStr(txtAdress(iAD_Strasse).Text)
             .FldValList_Add "AD_Land", .strStr(txtAdress(iAD_Land).Text)
             .FldValList_Add "AD_Plz", .strStr(txtAdress(iAD_Plz).Text)
             .FldValList_Add "AD_Ort", .strStr(txtAdress(iAD_Ort).Text)
             .FldValList_Add "AD_Geburtsdatum", _
                              .strDate(txtAdress(iAD_Geburtsdatum).Text, asAdoDate)
             .FldValList_Add "AD_Geld", .strNum(txtAdress(iAD_Geld).Text)
             .FldValList_Add "AD_Kontakte", .strNum(txtAdress(iAD_Kontakte).Text)
             .FldValList_Add "AD_Gewichte", .strNum(txtAdress(iAD_Gewichte).Text)
             If ID = 0 Then
                'bei Erstanlage
                .FldValList_Add "AD_Created", .strDate(Now, asADODateTime)
                .FldValList_Add "AD_CreatedBy", .strStr(GetCurrentUserName)
                sSQL = "Insert Into Adressen " & .GetSQL_Insert
                Msg = "Fehler bei Insert"
             Else
                'bei Update
                .FldValList_Add "AD_Update", .strDate(Now, asADODateTime)
                .FldValList_Add "AD_UpdateBy", .strStr(GetCurrentUserName)
                
                sSQL = "Update Adressen " & .GetSQL_Update & _
                       "Where AD_ID = " & ID
                Msg = "Fehler bei Update"
             End If
          End With
          'zum Testen
    '      Debug.Print sSQL
          On Error GoTo Fehler
          'Insert/Update
          Cn.Execute sSQL, Records
          If Records = 0 Then
             Msg = Msg & vbCrLf & vbCrLf & _
                   "die Anweisung wurde nicht ausgeführt" & _
                   vbCrLf & vbCrLf & sSQL
             MsgBox Msg, vbCritical, "RecordAddUpdate"
          Else
             If ID = 0 Then
                'bei Insert neue ID holen
                ID = cSQL.GetNewID(Cn)
             End If
             RecordAddUpdate = ID
          End If
    Fehler:
          If Err.Number <> 0 Then
             FehlerAnzeige Err.Number, Err.Description, "RecordAddUpdate"
          End If
          On Error GoTo 0
    End Function

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    759

    Re: Display records between two dates

    You can never trust anything entered by the User, exemplified by the classic "Little Bobby Tables".

    Also, and more specifically to your question, Parameterised Queries eliminate any difficulties with formatting date literals, such as those in your SQL:

    When is "01-03-05"?
    • January 3rd?
    • March 1st?
    • March 5th (2001)?

    All of these could be right, depending on your chosen Regional Settings.


    Regards,
    Phill W.

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Display records between two dates

    Hi Phill

    yes I know, Users can drive you mad

    here a sample with Dates, if you want to change to the Format
    give it a try

    this was a wish from Users in the Accountdept. for fast Input say the just Typ 16
    and the rest is added like 16.10.2017 (Germany)

    sample...
    Code:
    Private Sub Text1_Validate(Cancel As Boolean)
    
    'Enter only 16 = Returns the 16.10.2017
    
    Dim Msg As String
    Dim Value As String
                If Not Date_Input_OK(Text1) Then
                   Msg = "ungültiges Datum" & Space(20)
                   MsgBox Msg, vbCritical, "Fehler"
                   Cancel = True
                   Text1.SelStart = 0
                   Text1.SelLength = Len(Text1)
                   Exit Sub
                End If
    End Sub
    
    Public Function Date_Input_OK(Tbox As TextBox) As Boolean
    'Eingabe eines Datums überprüfen
    
       Dim s() As String
       Dim zw As String
       
          zw = Tbox.Text
          'in deutsches Format wandeln
          zw = Trim(Replace(zw, ",", "."))
          zw = Replace(zw, ":", ".")
          
          'keine Eingabe
          If Len(zw) = 0 Then
             Date_Input_OK = True
             Exit Function
          End If
          
          'aufteilen nach Tag, Monat, Jahr
          s() = Split(zw, ".")
          
          If UBound(s) = 0 Then
             'nur Tag angegeben
             ReDim Preserve s(2)
             s(1) = Month(Now)
             s(2) = Year(Now)
          ElseIf UBound(s) = 1 Then
             'Tag & Monat angegeben
             ReDim Preserve s(2)
             s(2) = Year(Now)
          ElseIf UBound(s) > 2 Then
             'mehr als 2 Punkte gefunden
             Exit Function
          End If
          
          If Len(s(2)) = 0 Then
             'Jahr wurde nicht angegeben
             If Len(s(1)) = 0 Then
                'auch Monat nicht angegeben
                Exit Function
             End If
             s(2) = Year(Now)
          End If
          
          If Len(s(2)) > 4 Then
             'Jahr > 4-stellig
             Exit Function
          End If
          
          'wenn Jahr 1- oder 2-stellig
          'adden 1900 oder 2000
          If Val(s(2)) < 100 Then
             If Val(s(2)) < 80 Then
                s(2) = Str(Val(s(2)) + 2000)
             Else
                s(2) = Str(Val(s(2)) + 1900)
             End If
          End If
          
          'Tage überprüfen
          If Val(s(0)) = 0 Or Val(s(0)) > 31 Then
             'zugelassen nur 1 - 31
             Exit Function
          End If
          
          'Monat überprüfen
          If Val(s(1)) = 0 Or Val(s(1)) > 12 Then
             'zugelassen nur 1 bis 12
             Exit Function
          End If
          
          'Datum zusammenstellen
          zw = Join(s(), ".")
          
          'VB-Check
          If Not IsDate(zw) Then
             Exit Function
          End If
          
          'Datumsfeld füllen
          Tbox.Text = Format(zw, "dd.mm.yyyy") 'Germany
          'or
          Tbox.Text = Format(zw, "mm.dd.yyyy") 'UK
    
          
          Date_Input_OK = True
    End Function
    works now for over 15 Years, also the sample in Post 8

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Display records between two dates

    don't know what went wrong
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Display records between two dates

    just for the heck of it, and not using the 'between' operator (to avoid problems if there is a time component)
    Code:
    Private Sub Command2_Click()
        Dim Row As Integer
        Dim rs As ADODB.Recordset
        Dim fld As ADODB.Field
       
        Set rs = New ADODB.Recordset
        rs.Fields.Append "TheField0", adVarChar, 10
        rs.Fields.Append "TheField1", adVarChar, 10
        rs.Fields.Append "TheField2", adCurrency
        rs.Fields.Append "TheField3", adDate
        rs.Open
    
        For Row = 1 To 100
            rs.AddNew Array("TheField0", "TheField1", "TheField2", "TheField3"), Array("AA " & CStr(Row), "BB " & CStr(Row + 1), Rnd * 100, Date + Row)
        Next
        
        Set DataReport2.DataSource = rs
        DataReport2.Show vbModal
        
        rs.Filter = "TheField3 >= #2018-01-12#  AND TheField3 < #2018-01-20#"
    
        Set DataReport2.DataSource = rs
        DataReport2.Show vbModal
        
        rs.Close
    End Sub
    do not put off till tomorrow what you can put off forever

Posting Permissions

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



Click Here to Expand Forum to Full Width