CODEWITHSUNDEEP
sqlms-accessms-access-2010
Daniel Rohn
Daniel Rohn

Reputation: 21

DateDiff not functioning as expected, unable to troubleshoot

I'm trying to set up a query to pull records where the date the record was entered into the database, [AssignmentDate], is compared against today's date, via Now(), to determine whether the record is older than a value selected by the user, e.g. 30 days, 60 days, etc.

Here's the code I have to set a String, SelectionVariables, to the SQL I want:

SelectionVariables = "WHERE (DateDiff('d', [AssignmentDate], Now())>'" & _
                        CaseAge.Value & _
                        "');"

Case.Value is the user-selected value from a drop-down. I'm absolutely baffled by the result I get, with Case.Value = 30: the query pulls two records that are dated 6/1/2015 and 5/1/2015, as expected. It does not pull the two records dated 7/15/2015 and 8/1/2015, as expected. It does not pull the record dated 1/1/2015, and for the life of me I cannot diagnose why it isn't getting that one. I haven't found any information indicating that DateDiff has some sort of range that I am exceeding, so I'm left baffled.

Your magical expertise is most appreciated.

Upvotes: 0

Views: 136

Answers (1)

Daniel Rohn
Daniel Rohn

Reputation: 21

HansUp's comment fixed the issue:

What happens if you change your code to this? SelectionVariables = "WHERE >DateDiff('d', [AssignmentDate], Now())>" & CaseAge.Value & ";" – HansUp 24 mins >ago

It appears that the extra parentheses around my original expression were the issue:

(DateDiff('d', [AssignmentDate], Now())>'" & CaseAge.Value & '"');

Not certain why those parentheses cause the expression to evaluate incorrectly, but so it is.

Upvotes: 1

Related Questions