How to do a query between hours

darnelleF

New member
Local time
Yesterday, 23:54
Joined
Feb 10, 2016
Messages
4
So i am trying to write a function that will get the number of records between todays date and now, but its been failing

Dim dbs As DAO.Database
Dim rstBlock As DAO.Recordset
Set dbs = CurrentDb
Set rstBlock = dbs.OpenRecordset("select * from Customers where " & _
"AddedBy = '" & EmployeeID & "' and WhenAdded BETWEEN " & Format$(Date, "mm\/dd\/yy\/hh:mm:ss A2") & " AND " & Now)


Number = rstBlock.RecordCount

i tried formating date so i can do the comparison but no luck here

any help will be greatly appreciated :D
 
you don't need to build DAO database nor recordsets. Access is already a database.
just make a query and run : docmd.openquery "myquery"
or using sql, docmd.runsql "select * from table"
if you want a record count : Dcount("*","myquery")

the sql can be made with simple date range:
make a query using a form to set the date start and end.
you don't have to format the dates. Dates are dates as they are in any format.

the query = select * from table where [datefield] between forms!myForm!txtStartDate and forms!myform!txtEndDate
 
you don't need to build DAO database nor recordsets. Access is already a database.
just make a query and run : docmd.openquery "myquery"
or using sql, docmd.runsql "select * from table"
if you want a record count : Dcount("*","myquery")

the sql can be made with simple date range:
make a query using a form to set the date start and end.
you don't have to format the dates. Dates are dates as they are in any format.

the query = select * from table where [datefield] between forms!myForm!txtStartDate and forms!myform!txtEndDate

so i should but a labels on the form which will have the start date and end date

cause im looking for something like
[datefield] between 2017/02/02 12:00:00 AM and 2017/02/02 4:45:56 PM

and each day it updates itself
 
when using date data in SQL you need to use date delimiters, like...
Code:
[datefield] between #2017/02/02 12:00:00 AM# and #2017/02/02 4:45:56 PM#
...otherwise the data is not recognized as a date, and the comparison fails.
Code:
Set rstBlock = dbs.OpenRecordset( _
   "SELECT * " & _
   "FROM Customers " & _
   "WHERE AddedBy = '" & EmployeeID & "' " & _
      "AND WhenAdded BETWEEN #" & Format$(Date, "mm\/dd\/yy\/hh:mm:ss A2") & "# AND #" & Now & "#")
 

Users who are viewing this thread

Back
Top Bottom