CODEWITHSUNDEEP
ms-accessms-access-2010
james.sw.clark
james.sw.clark

Reputation: 357

Microsoft Access 2010: Query Criteria moving

In my database, I have entered a query page in Design View, and entered the following two headers in separate columns: 

 MonthsSince: DateDiff("m",[LastContact],Now())

 LastContactError: DateDiff("m",[LastContact],Now())

The criteria I'd like to set are: ">6" and "<0"

The >6 criteria filters results so that the user knows that it has been six months since the person in question has been contacted THe <0 criteria show erroneous dates, specifically if a user put the LastContact date as being in the future

The problem I'm having is that Microsoft Access 2010 merges these two criteria into the rightmost field (LastContactError), and thus no results are returned (there are no numbers >6 and <0).

Even with just the >6 criteria in the MonthsSince field, after one successful search this criterion is moved to the LastContactError field.

Edit 1: I tried putting on criterion in the "criteria" row, and one in the "or" row, and got the same problem.

Edit 2:

I realised that this single query checks that both the conditions are met, not that either of them are met! The same thing is being compared: LastContact and Now().

Solution: Create two queries, with separate criteria

Upvotes: 0

Views: 85

Answers (1)

james.sw.clark
james.sw.clark

Reputation: 357

Solution: Create two queries, with separate criteria.

Currently, this single query is trying to meet both criteria - which are based off the same comparison. Obviously, it fails.

Upvotes: 1

Related Questions