Query Issue

[MaineLady]

I have created several queries and set the criteria for each to pull data based on the value of one of the fields in the database. The criteria is being ignored. Each query is giving me all of the data from the database. All of the queries worked when I created them a week ago.

[June7]

Something changed.

Would have to review the query SQL statement and/or the db. If you want to provide, follow instructions at bottom of my post.

[MaineLady]

I copied the db to my desktop. When I try to open it, I dialog box opens which says "Your Microsoft Access database or project contains a missing or broken reference to the file 'OWC10.DLL' version 1.0."

"* to ensure that your database or project works properly, you must fix this."

I can't close the dialog box, and also can't close Access.

Actually, there are more broken reference messages...

ABFIELD.OCX version 1.0 (7 or 8 times)

OWC10.DLL version 1.0 (twice)

And then the dialog box closes and I can close Access.

[MaineLady]

I think I got through all of the steps on the copy of the file. Weird thing... Original file is 2'932 KB and the copy is 768 KB.

However, when I tried to upload the file it told me it exceeded the 500 KB limit for the file type.

[MaineLady]

Sometimes... If I talk to myself long enough, I figure some things out on my own.... LOL

CACIWC Database - Copy.mdb

Hope I did this correctly.

[ssanfu]

Both queries appear to be returning the correct records.

The table has a total of 529 records.

For the query "_FAIRFIELD COUNTY", I executed it "as is". The results was 238 records.
I changed the criteria to only [CNTY] = "F" and [S/C/I] = "C". The number of returned records = 10
I changed the criteria to only [S/C/I] = "S". The number of returned records = 228



For the query "_HARTFORD COUNTY", I executed it "as is". The results was 238 records.
I changed the criteria to only [CNTY] = "H" and [S/C/I] = "C". The number of returned records = 10
I changed the criteria to only [S/C/I] = "S". The number of returned records = 228


The queries seem to be working perfectly.

[MaineLady]

Sorry, but it is not working perfectly. A query for [CNTY="H" and [S/C/I]="C" or "S" should only return 35 records. The 238 records returned includes all of the possible values in [CNTY]. The criteria established for [S/C/I] is working. The criteria established for [CNTY] is not.

[ssanfu]

The queries are working perfectly the way they are written. They are returning what you asked for.

You have asked for all records where ([CNTY]="H" AND [S/C/I]="C") OR [S/C/I]="S". <-(returns 238 records)

That is different than asking for all records where
[CNTY]="H" AND ([S/C/I]="C" OR [S/C/I]="S") <-(returns 39 records)

Notice the parenthesis.

This query for HARTFORD returns 39 records:

SELECT CACIWC_DATA.TOWN, CACIWC_DATA.[Crnt FY], CACIWC_DATA.[FIRST NAME], CACIWC_DATA.[LAST NAME], CACIWC_DATA.ROLE, CACIWC_DATA.[HOME ADDRESS (CHAIR)], CACIWC_DATA.[HOME PHONE (CHAIR)], CACIWC_DATA.[HOME E-MAIL (CHAIR)], CACIWC_DATA.[TOWN PHONE], CACIWC_DATA.[TOWN E-MAIL], CACIWC_DATA.[TOWN WEBSITE], CACIWC_DATA.[COMMISSION NAME], CACIWC_DATA.[TOWN ADDRESS], CACIWC_DATA.[MAIL TOWN], CACIWC_DATA.STATE, CACIWC_DATA.ZIP, CACIWC_DATA.CNTY, CACIWC_DATA.[S/C/I], CACIWC_DATA.SUS, CACIWC_DATA.[STAFF PERSON], CACIWC_DATA.[STAFF PHONE], CACIWC_DATA.[STAFF EMAIL]
FROM CACIWC_DATA
WHERE (((CACIWC_DATA.CNTY)="H") AND ((CACIWC_DATA.[S/C/I])="C" Or (CACIWC_DATA.[S/C/I])="S"))
ORDER BY CACIWC_DATA.TOWN;

This query for FAIRFIELD returns 35 records:

SELECT CACIWC_DATA.TOWN, CACIWC_DATA.[Crnt FY], CACIWC_DATA.[FIRST NAME], CACIWC_DATA.[LAST NAME], CACIWC_DATA.ROLE, CACIWC_DATA.[HOME ADDRESS (CHAIR)], CACIWC_DATA.[HOME PHONE (CHAIR)], CACIWC_DATA.[HOME E-MAIL (CHAIR)], CACIWC_DATA.[TOWN PHONE], CACIWC_DATA.[TOWN E-MAIL], CACIWC_DATA.[TOWN WEBSITE], CACIWC_DATA.[COMMISSION NAME], CACIWC_DATA.[TOWN ADDRESS], CACIWC_DATA.[MAIL TOWN], CACIWC_DATA.STATE, CACIWC_DATA.ZIP, CACIWC_DATA.CNTY, CACIWC_DATA.[S/C/I], CACIWC_DATA.SUS, CACIWC_DATA.[STAFF PERSON], CACIWC_DATA.[STAFF PHONE], CACIWC_DATA.[STAFF EMAIL]
FROM CACIWC_DATA
WHERE (((CACIWC_DATA.CNTY)="F") AND ((CACIWC_DATA.[S/C/I])="C" Or (CACIWC_DATA.[S/C/I])="S"))
ORDER BY CACIWC_DATA.TOWN;

[MaineLady]

I appreciate your help, Steve, but I don't understand what you're saying. I am going to post an Excel file that I exported the Fairfield County Query to a week ago... I created 9 of these queries and they all gave me what I wanted on 10/20. I didn't do anything to them, so I don't understand how/why they are now giving different results.

I can't attach the excel file, and I don't have time to deal with it right now. I see the parenthesis in the SQL files that you sent me. I will alter my queries to mimic what you sent. I still don't understand how or why it changed.

[ssanfu]

I don't know how or why the query would change.
These are images of the queries.

The first image is how the query is in the dB you posted:
Attachment 18530

This how the query looks after I modified it:
Attachment 18531

Two totally different queries...

[MaineLady]

Again, Steve... Thank you for your help. I changed the way the query is written and it's working fine. But I am not kidding around when I tell you that I created 9 queries, using query wizard last Monday (10/20) and they all gave me the data that I wanted. Who knows why.

Thank you, again.

Janice

[ssanfu]

Glad you got it working the way you want.

[June7]

I avoid most of the design wizards because they do annoying things.