I have a database with these fields:
Name String
DateTime1 Date/time
DateTime2 Date/time
I want to select records where the difference between DateTime2 and DateTim1 is above a specified number of minutes.
The code below works if I want to select records where the difference between the two DateTimefields is longer than 4 minutes. (0.00277777777777778 = 4 minutes)
Sub QueryTest1()
Dim strQuery As String
Dim dbs As Database
Set dbs = CurrentDb
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryDisplayResult" 'Delete query if exist
strQuery = "SELECT tblTest.Name, Count(tblTest.Name) AS AntalförName " _
& "FROM tblTest " _
& "WHERE ((([date2] - [date1]) > 6.94444444444444E-04)) " _
& "GROUP BY tblTest.Name;"
Call CurrentDb.CreateQueryDef("qryDisplayResult", strQuery) 'Create the query
DoCmd.OpenQuery "qryDisplayResult" 'Display the query
End Sub
I want to create a more flexible code where I can specify the time (whole minutes) in an Inputbox. My problem is that I use a Swedish version of Access 2010 and we use a comma as decimal limiter and not a point. When I calculate minutes from the inputbox with 0.000694444444444444 (= 1 minute) the result shows with a decimal comma ant not with a point witch VBA requires.
Sub QueryTest2()
Dim strQuery As String
Dim intMinutes As Integer
Dim dblMinutesAsDecimal As Double
Dim dbs As Database
Set dbs = CurrentDb
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryDisplayResult" 'Delete query if exist
intMinutes = InputBox("Select minutes")
dblMinutesAsDecimal = intMinutes * 6.94444444444444E-03
strQuery = "SELECT tblTest.Name, Count(tblTest.Name) AS AntalförName " _
& "FROM tblTest " _
& "WHERE ((([date2] - [date1]) > " & dblMinutesAsDecimal & ")) " _
& "GROUP BY tblTest.Name;"
Call CurrentDb.CreateQueryDef("qryDisplayResult", strQuery) 'Create the query
DoCmd.OpenQuery "qryDisplayResult" 'Display the query
End Sub