Troubleshooting Document Search Form Data Retrieval Issues

@Walter Laidlaw there was no SQL statement attached to your reply. Simply view the SQL view and copy it into a reply. It would help if you could attach a copy of your database or at least a new db with the required objects.

Here's the SQL view:

SELECT [Documents Table].DocID, [Documents Table].Category, [Documents Table].DocumentFamily, [Documents Table].DocumentType, [Documents Table].DigitalFileName, [Documents Table].Year, [Documents Table].City, [Documents Table].State, [Documents Table].Country, [Documents Table].[FName 1], [Documents Table].[LName 1], [Documents Table].[FName 2], [Documents Table].[LName 2]

FROM [Documents Table]

WHERE ((([Documents Table].Category) Like "" & Forms!DocSearchF!CatagorySearch & "" Or ([Documents Table].Category) Is Null) And (([Documents Table].DocumentFamily) Like "" & Forms!DocSearchF!FamilySearch & "" Or ([Documents Table].DocumentFamily) Is Null) And (([Documents Table].DocumentType) Like "" & Forms!DocSearchF!TypeSearch & "" Or ([Documents Table].DocumentType) Is Null) And (([Documents Table].DigitalFileName) Like "" & Forms!DocSearchF!DigitalFileSearch & "" Or ([Documents Table].DigitalFileName) Is Null) And (([Documents Table].Year) Between Forms!DocSearchF!YearMinSearch And Forms!DocSearchF!YearMaxSearch) And (([Documents Table].City) Like "" & Forms!DocSearchF!CitySearch & "" Or ([Documents Table].City) Is Null) And (([Documents Table].State) Like "" & Forms!DocSearchF!StateSearch & "" Or ([Documents Table].State) Is Null) And (([Documents Table].Country) Like "" & Forms!DocSearchF!CountrySearch & "" Or ([Documents Table].Country) Is Null) And (([Documents Table].[FName 1]) Like "" & Forms!DocSearchF!FName1Search & "" Or ([Documents Table].[FName 1]) Is Null) And (([Documents Table].[LName 1]) Like "" & Forms!DocSearchF!LName1Search & "" Or ([Documents Table].[LName 1]) Is Null) And (([Documents Table].[FName 2]) Like "" & Forms!DocSearchF!FName2Search & "" Or ([Documents Table].[FName 2]) Is Null) And (([Documents Table].[LName 2]) Like "" & Forms!DocSearchF!LName2Search & "" Or ([Documents Table].[LName 2]) Is Null))

ORDER BY [Documents Table].DocID;

Can't attach the database file as it's way larger than 3MB. I will try to create a new db, but it will take me awhile.

The first thing to address is that your Documents table is not normalized. It is in need of decomposition into a set of related tables along the lines of the tables in the bottom part of the model illustrated below, which is taken from my DatabaseBasics.accdb demo file, which you'll find in DatabaseBasics.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

At present, by including all the members of hierarchy City---<States---<Countries as columns in the Documents table the last two are transitively determined by the key, in violation of Third Normal Form (3NF), which requires all non-key columns to be determined solely by the whole of the key.

For data input into a correctly normalized model take a look at the first two forms in the section in the demo on 'entering data via a form/subforms'.

When it comes to data retrieval the section on 'retrieving data from the database' illustrates a number of scenarios. The first form in this section illustrates, in the right hand subform, how to search on the basis of one or more parameters, allowing each parameter to be optional. This done by the following query:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region, 
Country, Employer, LastName, FirstName, Contacts.ContactID
FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID) 
INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts 
ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers 
ON Contacts.ContactID = ContactEmployers.ContactID) 
ON Employers.EmployerID = ContactEmployers.EmployerID) 
ON Regions.RegionID = Cities.RegionID
WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity
  OR Forms!frmReportDialogue!cboCity IS NULL)
AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer
  OR Forms!frmReportDialogue!cboEmployer IS NULL);

As you can see, each parameter is examined for OR <parameter> IS NULL in a parenthesized OR operation. This allows the parameter to be left blank.

The second form in the section on 'retrieving data from the database' illustrates the use of a similar query, but in this case the search is made progressively by drilling down via country, region, and then city in correlated combo boxes

Note that in none of the above search routines is the wildcard * character used. In these examples the use of combo boxes restricts the available search parameter values to those present in the tables, so an exact term can always be used in each case.

Another important point to note is that the keys on which the query is restricted are numeric values, not the text values such as city names. This is essential when searching on personal names or city names for instance, which can both be legitimately duplicated.

To: Ken SheridanWhen I try to open your answer, it does not come up.

Walt