Convert decimal comma to decimal point

[Hans Karlsson]

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

[June7]

What result - the query output? Why is decimal comma display an issue?

[Missinglinq]

Instead of using [date2] - [date1], why not use the DateDiff() function:

DateDiff("n"), [date1], [date2]) > NumberOfMinutes

Linq ;0)>

[June7]

Good catch linq. Subtraction of dates defaults to day unit. As linq suggests, instead of converting the minutes input to decimal day, use DateDiff and specify minutes with "n" parameter.