Query by form does not show all records

[011billyw]

hi thanks for the help!
Well i have a Query with 5Yes/no fields, 1 yes/no is hidden(with criteria Yes) and the others have a criterias a criteria i have [Forms]![ProjectMarketSectorSetUp]![Terminals], .....

The form Works good when i have only one field selected and the other deselected, but i don't know how to make it show all the Records., do i have to make another button with a Coding?

i Will only be queriing one of the 4 fields at the time, but i want the user to be able to see all the records if that is what they want to.



Eventually i will make this a report, but i need the query to work first, can someone help me?

Thank you in advanced.

[June7]

Would you like to provide the project for analysis?

[011billyw]

i will try, because i need pemision from the person i am doing this for, since it has personal contacts and information

[June7]

Make copy, remove confidential data, run Compact & Repair, zip if large.

[011billyw]

ey bro do u thnik you can give me your email becuz even when i deleted it it has 100kb, and compresed is 94 kb

[June7]

500 kb Access file and 2mb zip file allowed as post attachments

Otherwise, upload to fileshare site such as box.com and post link to file.

[011billyw]

i will try to uploadit now

http://www.4shared.com/file/FgG7g-pq...l?refurl=d1url

if it doenst work tell me pls

thanks in advanced for time

[June7]

I have never seen so many yes/no fields in one database. This does make data entry easy but data is not normalized. Using multiple yes/no fields as criteria with conditional parameters is tricky. It is the nature of yes/no fields. This should work (just copy/paste the statement into SQL view of a new query then switch to Design view):

SELECT [Projects Info].[Project Number], [Projects Info].[Project Name], [Projects Info].[Facility Name], [Projects Info].Client, [Projects Info].Owner
FROM [Market Sub-Sectors] INNER JOIN [Projects Info] ON [Market Sub-Sectors].ID = [Projects Info].[Market Sub-Sectors ID]
WHERE ((([Projects Info].Aviation)=Yes) AND ((IIf([Cargo Facilities]=True And [Forms]![ProjectMarketSectorSetUp]![Cargo Facilities]=True,True,False))=True)) OR ((([Projects Info].Aviation)=Yes) AND ((IIf([Run, Taxiways, Apron]=True And [Forms]![ProjectMarketSectorSetUp]![Run, Taxiways, Apron]=True,True,False))=True)) OR ((([Projects Info].Aviation)=Yes) AND ((IIf([Airport Facility]=True And [Forms]![ProjectMarketSectorSetUp]![Airport Facility]=True,True,False))=True)) OR ((([Projects Info].Aviation)=Yes) AND ((IIf([Terminals]=True And [Forms]![ProjectMarketSectorSetUp]![Terminals]=True,True,False))=True))
ORDER BY [Projects Info].[Project Number], [Projects Info].[Facility Name];

However, there might be a flaw in the data. Project 116-304 in Projects Info is checked as Aviation but none of the aviation fields in the related Market Sub-Sectors table record are checked. This means the above criteria with the highlighted AND operator fails to return records. Change the AND to OR and one record will return.

[011billyw]

yes sorry about this, is becuae i used the information from an excel file, and making a relationship would have extended the projects for several weeks of months.

the database has 1400 projects each with different market sectors, and there is 2 other projects,

i will try it and let u know but for now i have to go to skool

that is becasue there can be an aiport project that has a police station wich would qualify as institutional, but the fact the is an airport is still there

[011billyw]

ey bro i tried it with AND,(didnt work) with or (It Worked)

but still in the form ProjectReportSetUp, doesn't show all the items when none Check boxes are selected.

This is what i want to do, i want the user be able to see the yes no boxes he selected, in a way i want those boxes to filter the information, but not to double filter,
Lets say the user wants to see the airport , but only air facility and cargo facility,

so the query should show all the facility and cargo facility projects. The original file shows only projects that are both( this is where i have the problem)

thanks for the time June 7!

[June7]

Don't understand problem. I tested the query and it works. Had to set some Market Sub-Sectors records to the various Aviation types. Any related to a Projects Info record and matching any of the checked aviation items were retrieved as were any Projects identifed as Aviation.

The QryProjectAirport is an INNER join so some Market Sub-Sectors records that are coded to an Aviation type but aren't associated with a project won't display. Every project has a related Market Sub-Sectors record so all projects coded as Aviation are available.

[011billyw]

Yyes bro sorry about this, it does work witht the AND, i changed it

Thanks so much

For the rest i Just base the SQL format in the one you gave me right?

[011billyw]

bro i was trying to do it , but i have no idea how to rearrange the coding

[June7]

You changed query to use the AND? I thought the OR was what you wanted?

Rearrange what code?

[011billyw]

Actually the OR didnt work, since it didnt show any info, while the AND does what i wanted all along

now the only problem is that i want to do the same for the rest of the market sectors, but i cannot do that in sql

and there is a a sector that has 12 subsector, and i saw in the design view that u have a tru tru false,( so ishould have the 12 true true false,

i dont know how to work it out