Displaying entire contents of search results?

[ki2n]

I'm trying to make a search form that makes use of queries that search a table based on what fields I have. How would I go about having the query display all the fields for the record(s) that match the search criteria? This is for Access 2010.

As far as the query is set up, I have several fields with a search criteria, and the others are for displaying the relevant information about the results (since they're not search criteria, I used "Like '*'"). When I ran the query, it doesn't come up with anything - even if the record actually exists in the table with specified criteria.

 

[JHB]

What result do you get if you not use "Like '*'"?

 

[gbnz]

If every field of your search criteria is set to "Like '*'" then every field will have to be populated with something. "Like '*'" removes nulls from the query.

 

[ki2n]

What result do you get if you not use "Like '*'"?

Nothing changed. I'm still getting no records.

If every field of your search criteria is set to "Like '*'" then every field will have to be populated with something. "Like '*'" removes nulls from the query.

That's what I'm trying to aim for - a single query that could search records based on several criteria, where criteria are selectable by the form. Therefore, I thought of using the "Like" statements so that it would select all the records that weren't selected as search criteria. I know I could just make a query for each occasion of criteria, but it might take a while to make for each possibility.

 

[JHB]

Show your query or even better, post a stripped version of your database with some sample data (zip it), + information about in which form/query you have the problem.

 

[gbnz]

If every field of your search criteria is set to "Like '*'" then every field will have to be populated with something. "Like '*'" removes nulls from the query.

I am sorry I miss spoke in my post " "Like '*'" removes records with nulls from the query."

Here is an example of what I am trying to explain.

Say you have a table with 3 columns City State Zip and it is populated like the following

City | State | Zip |
Chicago | IL | 60600 |
New York | NY | |
St.Louis |MO | 63101 |
Colorado Springs | | 80901 |

and you create a query with City State Zip all with Like '*' Criteria. Your query will return this:
Chicago | IL | 60600 |
St.Louis |MO | 63101 |

Because the New York and the Colorado Springs records have a null in them.

Hope this helps!

 

[ki2n]

I am sorry I miss spoke in my post " "Like '*'" removes records with nulls from the query."

If so, then how would I be able to include those fields? (Some records have fields that have null values, while others have the same fields filled in, but have null values in others).

Show your query or even better, post a stripped version of your database with some sample data (zip it), + information about in which form/query you have the problem.

If I were to show the query, do I show it in SQL format?

 

[JHB]

Yes in SQL format.

 

[ki2n]

SELECT Enrollee_Data.FirstName, Enrollee_Data.MiddleName, Enrollee_Data.LastName, Enrollee_Data.RegistrationDate, Enrollee_Data.BirthDate, Enrollee_Data.Race_Ethnicity, Enrollee_Data.Country_ofOrigin_Ethinicity, Enrollee_Data.Gender, Enrollee_Data.Street_Adrs1, Enrollee_Data.Street_Adrs2, Enrollee_Data.City, Enrollee_Data.State, Enrollee_Data.ZipCode, Enrollee_Data.Borough, Enrollee_Data.Home_Phone, Enrollee_Data.Cell_Pager, Enrollee_Data.eMail, Enrollee_Data.Social_Sec_Num, Enrollee_Data.OSIS_Num, Enrollee_Data.Lives_With
FROM Enrollee_Data
GROUP BY Enrollee_Data.FirstName, Enrollee_Data.MiddleName, Enrollee_Data.LastName, Enrollee_Data.RegistrationDate, Enrollee_Data.BirthDate, Enrollee_Data.Race_Ethnicity, Enrollee_Data.Country_ofOrigin_Ethinicity, Enrollee_Data.Gender, Enrollee_Data.Street_Adrs1, Enrollee_Data.Street_Adrs2, Enrollee_Data.City, Enrollee_Data.State, Enrollee_Data.ZipCode, Enrollee_Data.Borough, Enrollee_Data.Home_Phone, Enrollee_Data.Cell_Pager, Enrollee_Data.eMail, Enrollee_Data.Social_Sec_Num, Enrollee_Data.OSIS_Num, Enrollee_Data.Lives_With
HAVING (((Enrollee_Data.FirstName)=[Forms]![frmMain]![NavigationSubform]![Text24]) AND ((Enrollee_Data.MiddleName)=[Forms]![frmMain]![NavigationSubform]![Text28]) AND ((Enrollee_Data.LastName)=[Forms]![frmMain]![NavigationSubform]![Text32]) AND ((Enrollee_Data.Street_Adrs1)=[Forms]![frmMain]![NavigationSubform]![Text36]) AND ((Enrollee_Data.Street_Adrs2)=[Forms]![frmMain]![NavigationSubform]![Text38]) AND ((Enrollee_Data.City)=[Forms]![frmMain]![NavigationSubform]![Text41]) AND ((Enrollee_Data.State)=[Forms]![frmMain]![NavigationSubform]![Text43]) AND ((Enrollee_Data.ZipCode)=[Forms]![frmMain]![NavigationSubform]![Text45]) AND ((Enrollee_Data.Borough)=[Forms]![frmMain]![NavigationSubform]![Text49]) AND ((Enrollee_Data.Home_Phone)=[Forms]![frmMain]![NavigationSubform]![Text49]) AND ((Enrollee_Data.Cell_Pager)=[Forms]![frmMain]![NavigationSubform]![Text49]) AND ((Enrollee_Data.Social_Sec_Num)=[Forms]![frmMain]![NavigationSubform]![Text52]) AND ((Enrollee_Data.OSIS_Num)=[Forms]![frmMain]![NavigationSubform]![Text53]));

 

[JHB]

For each criteria you have insert the OR-part as I've done below for 2 examples.
Take care of the ().

((((Enrollee_Data.FirstName)=[Forms]![frmMain]![NavigationSubform]![Text24] OR [Forms]![frmMain]![NavigationSubform]![Text24] Is Null)) AND (((Enrollee_Data.MiddleName)=[Forms]![frmMain]![NavigationSubform]![Text28] OR [Forms]![frmMain]![NavigationSubform]![Text28] Is Null)) AND ....

If you can't get it to work, post a database with the form and the table with some sample data in it, and also the query, (zip it).