Date Parameters in Query Not Always Restricting Data

[PATRICKPBME]

I just started using Access 2010 today and ran into a snag with a query. Here is the situation. I am doing reports for all contracts in my table active between 1-1-17 and 12-31-17. That date is in my "end" column that I put in my query. Also, I have to put in the "responsible parties" which are found in that column. So far; so good. For some reason, when I run the query it brings up contracts outside of the parameters I have set.

I seem to have this problem only when there is more than one person listed in the "responsible" cell for a particular contract. For example, if I have Mary as the responsible person for a contract ending 10-5-17 there is no problem. However, if I have Mary and John as responsible for a contract ending 6-3-18, Access is going outside of my date parameters and adding that contract to my list.



I have made sure the information placed in the query is correct. I am guessing that maybe Access is saying, "Hey, I can't find anything for John and Mary together in 2017, so I'll go and find you something further down the line." I need to tell Access NOT to do that.

Thanks in advance.

[pbaldy]

What is the SQL of the query? More likely, you've got OR instead of AND between criteria.

[PATRICKPBME]

I am guessing that by "SQL" you are referencing what I type in. I type in Between 1-1-17 and 12-31-17 which results in Between #1/1/2017# And #12/31/2017# Still get results outside of these parameters.

[pbaldy]

No, the design grid is an interface to create SQL. Click on View in the ribbon and select SQL view.

[PATRICKPBME]

Got it! Okay, when I do this it works fine:

SELECT PhysicianT.EndDate, PhysicianT.MedProf, PhysicianT.Comments, PhysicianT.ResponsibleParty
FROM PhysicianT
WHERE (((PhysicianT.EndDate) Between #1/1/2017# And #12/31/2017#) AND ((PhysicianT.ResponsibleParty)="Christy Martin"));

But when I add some names where Christy is working with someone else, it brings back results outside of the parameters set. Below is what results in that.

SELECT PhysicianT.EndDate, PhysicianT.MedProf, PhysicianT.Comments, PhysicianT.ResponsibleParty
FROM PhysicianT
WHERE (((PhysicianT.EndDate) Between #1/1/2017# And #12/31/2017#) AND ((PhysicianT.ResponsibleParty)="Christy Martin")) OR (((PhysicianT.ResponsibleParty)="Christy Martin, Kim Grossman")) OR (((PhysicianT.ResponsibleParty)="Christy Martin, April Hackney"));


Thanks again!

[aytee111]

It is the and's and or's that are confusing matters. In design view of the query, make sure that all criteria are on the same line. If not, you must repeat the date criteria for each line.

[andy49]

Hi Patrick.

I realise your data must be "secret" but could you create a version of physicianT with fake names? We will be able to help quicker I'm sure.


Sent from my iPhone using Tapatalk

[pbaldy]

If I take out the extraneous parentheses and separate, you'll see the problem:

WHERE

(PhysicianT.EndDate Between #1/1/2017# And #12/31/2017# AND PhysicianT.ResponsibleParty="Christy Martin")

OR

PhysicianT.ResponsibleParty="Christy Martin, Kim Grossman"

OR

PhysicianT.ResponsibleParty="Christy Martin, April Hackney"

In design view, I'm guessing there are criteria on different lines? That gives you an "OR" comparison. You need to repeat the date criteria on each line there's a person criteria.

[pbaldy]

I guess I can get out of the way.

[PATRICKPBME]

Thank you everyone. As a "newbie" I didn't realize that I could put a string with all different names on one line so was dropping down a line for each new group. Once I put things on the same line it worked fine. Thanks so much!! Problem solved!