-
Oct 12th, 2017, 07:11 AM
#1
Thread Starter
New Member
Display records between two dates
How to create a report which shows the records between two specific dates?
-
Oct 12th, 2017, 07:38 AM
#2
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.
-
Oct 12th, 2017, 11:03 AM
#3
Re: Display records between two dates
Last edited by TysonLPrice; Oct 12th, 2017 at 11:20 AM.
Please remember next time...elections matter!
-
Oct 13th, 2017, 11:55 AM
#4
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.
-
Oct 13th, 2017, 02:00 PM
#5
Re: Display records between two dates
Don't do what Donny Don't does.
Use a parameter query, avoid dynamic SQL when possible.
-
Oct 13th, 2017, 02:31 PM
#6
Re: Display records between two dates
Originally Posted by dilettante
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.
-
Oct 13th, 2017, 03:36 PM
#7
Re: Display records between two dates
-
Oct 14th, 2017, 01:43 AM
#8
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.
-
Oct 16th, 2017, 07:41 AM
#9
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.
-
Oct 16th, 2017, 09:21 AM
#10
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.
-
Oct 16th, 2017, 09:21 AM
#11
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.
-
Oct 16th, 2017, 04:11 PM
#12
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|