WHERE clause using Between and DateAdd not returning correct results (1 Viewer)

[Cosmos75]

I have the following two querydefs. Here is the SQL;

[b][COLOR="Blue"]qry1[/COLOR][/b]
SELECT [B][dtFind][/B], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE DateDiff("n",[dtReading],[B][dtFind][/B]) Between 1 And CInt([B][intMins][/B]);

[b][COLOR="blue"]qry2[/COLOR][/b]
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE dtReading Between DateAdd("n",-1*[B][intMins][/B],[B][dtFind][/B]) 
And DateAdd("n",-1,[B][dtFind][/B]);

[dtFind] and [intMins] are parameters.

I use the querydefs is VBA code as such

Dim db As DAO.Database
Dim rstDataSQL as DAO.Recordset
Dim qdfData As DAO.QueryDef
Dim strQdef As String

Set db = CurrentDb()
strQdef = "qry1" ‘or qry2
Set qdfData = db.QueryDefs(strQdef)

‘Set values of parameters
qdfData![dtFind] = dtDate
qdfData![intMins] = intMins

Set rstDataSQL = qdfData.OpenRecordset

qry2 executes significantly faster than qry1, but I am having issues getting the correct results.

If I set [dtFind] = 12/28/2005 10:47:00 AM, both queries work fine. The last returned record has a value for dtReading of 12/28/2005 10:46:00 AM.

However, if I set [dtFind] = 12/28/2005 10:48:00 AM, only qry1 returns the right records. qry2 will not return the record with dtReading = 12/28/2005 10:47:00 AM, but qry1 will.


I changed the Between statement to “Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n", 0 ,[dtFind])” to see what happens. As I expected, records where dtReading = 12/28/2005 10:47:00 AM and 12/28/2005 10:48:00 AM are returned.

The data should be in increments of 1 minute, although there are periods where data is missing. None of the dates have values like 12/28/2005 10:47:01 AM, i.e. seconds value is always 0.

I’ve tried adding “PARAMETERS [dtFind] DateTime, [intMins] Short;”, and also using CDate(DateAdd()) without any luck.

Whether I am using the queries using VBA/DAO or user input to set the parameter values, the results are the same.

What am I doing wrong?

 

[EMP]

In qry2, when intMins =1, the two values in the Between ... And ... are the same. So the query was actually doing an exact match.

Though 28/12/2005 10:47:00 AM is a visual display of an internal dbl number, it can be any one of a range of numbers, varying from - half a second to + half a second.

To test it, you can modify qry2 like this:-
SELECT [dtFind],
cdbl([tblData].[dtReading]) AS Expr1,
cdbl(DateAdd("n",-1*[intMins],[dtFind])) AS Expr2,
cdbl(DateAdd("n",-1,[dtFind])) AS Expr3,
Expr2=Expr3 AS Expr4,
Expr1-Expr2 AS Expr5
FROM tblData;

When dtReading=28/12/2005 10:47:00 AM, dtFind=28/12/2005 10:48:00 AM and intMins=1, you will see that Expr4 returns true while Expr5 shows there is a small difference of 7.27595761418343E-12 between Expr1 and Expr2, causing the exact match of Between ... And ... in the original qry2 to fail.

^

 

[Cosmos75]

EMP,

Thank you for your reply! I was afriad that it was going to be something like that.

I ran into the problem of subtracting decimals recently. I had read FMS's article - "When Microsoft Access Math Doesn't Add Up" before but didn't think of it until I was testing the code. So I am aware that numbers aren't exact representations.

I know that dates, as you said, are actually numbers with decimals (left of decimals = date, right of decimal = time) and was probably subject to some inexactness. I was just hoping that using DateAdd somehow got around any problems. Perhaps using -1 in DateAdd is a bad idea? It is somewhat worrisome to me that an SQL statement that seems to have proper syntax could cause a problem. I wouldn't have even caught it if I wasn't comparing and testing the two different SQL statements.

Is there a workaround to ensure that the proper dates are used? Or perhaps an alternative SQL statement that isn't as slow as qry1. Any ideas, anyone?

 

[Cosmos75]

This SQL statement seems to return the correct records.

[B]qry3[/B]
PARAMETERS [dtFind] DateTime, [intMins] Short;
SELECT [dtFind] AS dtDate, dtReading, dblValue
FROM tblData
WHERE dtReading >= DateAdd("n",-1*[intMins],[dtFind]) AND dtReading < [dtFind];

Just FYI, this SQL statement also works as well but is not as fast as qry3.

[B]qry4[/B]
PARAMETERS [dtFind] DateTime, [intMins] Short;
SELECT [dtFind] AS dtDate, dtReading, dblValue
FROM tblData
WHERE dtReading In (SELECT dtReading FROM tblData 
                    WHERE dtReading>=DateAdd("n",-1*[intMins],[dtFind]) And dtReading<[dtFind]);

Now my only concern is to figure out whether qry3 always returns the correct records.

 

[Cosmos75]

qry3 still wasn't pulling records properly. Was missing the start dates, i.e. the lower end of the criteria. So I made the folowing changed.

Changed

[B]qry3[/B]
PARAMETERS [dtFind] DateTime, [intMins] Short;
SELECT [dtFind] AS dtDate, dtReading, dblValue
FROM tblData
WHERE dtReading [COLOR="red"][b]>=[/b][/COLOR] DateAdd("n",-1*[COLOR="Red"][b][intMins][/b][/COLOR],[dtFind]) AND dtReading < [dtFind];

to

[B]qry3[/B]
PARAMETERS [dtFind] DateTime, [intMins] Short;
SELECT [dtFind] AS dtDate, dtReading, dblValue
FROM tblData
WHERE dtReading [COLOR="Blue"][b]>[/b][/COLOR] DateAdd("n",-1*[COLOR="blue"][b]([intMins]+1)[/b],[[/COLOR]dtFind]) AND dtReading < [dtFind];