I created a filter per period in vb with the following code:
Public Sub filtrar()
Dim dtinicio As DateTime
Dim dtfim As DateTime
Dim tabela As New DataTable
Dim strsql As String
dtinicio = dtpInicio.Value.ToLongDateString
dtfim = dtpFim.Value.ToLongDateString
strsql = "SELECT RegistoEntradaSaida.Movimento, RegistoEntradaSaida.DataHora, Utente.Nome, Relação.Descrição AS Relação, Relacionado.Nome AS Visitante, RegistoEntradaSaida.CartãoID, RegistoEntradaSaida.DataHoraEntrada FROM ((RegistoEntradaSaida INNER JOIN Relação ON RegistoEntradaSaida.RelaçãoID = Relação.ID) INNER JOIN Relacionado ON RegistoEntradaSaida.RelacionadoID = Relacionado.ID) INNER JOIN Utente ON RegistoEntradaSaida.UtenteID = Utente.ID Where RegistoEntradaSaida.DataHora between @dtInicio And @dtFim And Utente.Nome Like @Nome ORDER BY RegistoEntradaSaida.DataHora DESC;"
Dim cmd As New OleDbCommand
Try
CN.Open()
cmd.Connection = CN
cmd.Parameters.AddWithValue("@dtInicio", dtinicio)
cmd.Parameters.AddWithValue("@dtFim", dtfim)
cmd.Parameters.AddWithValue("@Nome", TextBox1.Text + "%")
cmd.CommandText = strsql
Using reader As OleDbDataReader = cmd.ExecuteReader()
tabela.Load(reader)
DataGridView1.DataSource = tabela
End Using
Catch ex As Exception
MessageBox.Show("Erro : " & ex.Message, "Consultar Registros")
Finally
CN.Close()
End Try
DataGridView1.Columns("DataHoraEntrada").DefaultCellStyle.Format = "HH:mm"
End Sub
The column RegistoEntradaSaida.DataHora
that I use in where
is datetime
. The filter works, but I have a small problem, I'll give you an example:
- If you put date start = 17-10-2018 and end date = 20-10-2018, in the database data return, returns the data from day 17, as placed on the start date, but only returns the data until the 19th, but I intended it to be until the 20th as I put it on the end date. Returns always the day before in relation to the day I place on the end date. Can anyone help?