Query is not reporting all results

[JerrodConaway]

I am creating a database in order to log in the location of certain files based. I began by creating an entry form with fields for "Individual", "Committee", "SubCommittee", "Issue", and "Location". Once entered into the form, the data is stored in a table. While I managed to do this with relative ease, I also created a search form that mimics the entry form in requested information.

However when creating the query to conduct a search I am missing data. Basically the query pulls information from the "Location" table, the "Individual" table, and the "Issues" table. Unfortunately if the "Individual" is left blank on the "Location" table, even if I am searching by issue, the query will exclude this entry in the final output. In otherwords, if I am searching by an issue, say education and there is an entry that is not related to an individual (in the Location table), that entry will on be displayed by the query, eventhough it falls under the parameters of the search

Any suggestions?

 

[PeterF]

Posting the SQL of your query would make it easier to see why you have problems finding the blanks.

If you use a like statement to filter your records you miss any empty fields (you are searching for a string and the field contains nothing, the "is null" would work for these).

Some workarounds:
You can change the source in the QBE grid to

YourNewFieldName: nz([YourFieldname],"")

or you can build your query in VBA and leaving the blank fields out of the SQL where part and for a report you only have to construct the where part. You can search the board for search forms to get some examples.

 

[JerrodConaway]

Sorry, here is the SQL of one of my queries:

SELECT SenateMember.SM_Last, SenateMember.SM_First, SenateMember.SM_MiddleName, SenateMember.SM_Nickname, Location.Committee, Location.SubCommittee, Issues.Issues, Location.Location
FROM (Location INNER JOIN SenateMember ON Location.SM_Seat = SenateMember.SM_Seat) INNER JOIN Issues ON Location.Issue = Issues.ID
WHERE (((Location.Issue)=[Forms]![Search]![CmbIssue]));

 

[WayneRyan]

Jerrod,

If you are developing your query in the Design Grid, right-click on the
line that joins the tables, and change the join property to a "type 2".
At least I think it is a "2". Feel free to experiment.

Then, you can right-click on your query when in Design View and select
"View SQL", you'll see that the Inner Joins have been replaced with
Left Joins.

If you are just typing in SQL, change the Inner Joins to Left Joins.
See above.

hth,
Wayne

 

[JerrodConaway]

Thanks for your help. I didnt think it would be that easy.