Possible malfunction on a query with datediff function.

[west]

I tried searching the forums for a while for similar problems but what I found didn’t help me.


One thing I tried was to change Date() for Now(), but no change, same problem occurs.


The problem described below was tested as of today 8/27/2014. In subsequent days it might react differently…


I have a table with [RegisterDate] and [Status]. When I run a query to give me records over 5 days it will only give me a 8 records. When I look at the table I see that more than 20 records are over 5 days old. Then I test with 10 or more days old and the result brings all the records in the table including those under 5 days.


I have included the db file for anyone’s testing.


Thanks all for you help.


-JC

 

[pbaldy]

It appears the criteria is being treated as text. Try a criteria of:

>CLng([Days])

 

[plog]

[Days] is being evaluated as a string. In turn, when it does the comparison it does the comparison as if both fields are strings. As strings these things are true:

1<2
2<3
20<3
3<4
3000<40

So, when you enter 10, you are actually being less restrictive than when you enter 9. Actually try that and see what you get--9 returns no values.

To fix this you must make sure >[Days] gets evaulated as a number. To do that use this as your criteria:

>[Days]*1

 

[west]

My! By far, I got more than what I asked for.

Both you approaches work very good.

I thank you guys for you quick and informative answers and I'm going to save both in my knowledge base.

-JC

 

[pbaldy]

Happy to help!