Creating a search on a form with a results table.

[soorags]

On the "View Directory" form of my database I would like to allow the user to be able to fill in AT LEAST one piece of information about a company so that when he or she clicks on the "Submit Information for Search" command button, the table below shows the results that have been searched for with all company information.

For example if only the company name "HMC" was typed in, clicking on the "Submit information for search button" would display all of the searched results in the table below, showing companies with the name "HMC"

However if HMC was typed in with the company's address as well, and then searched for, only the specific result would be shown.

How is it possible to make this happen on my database?

I would like to find out very soon, thank you.

Gurdip Soora.

 

[soorags]

When I used that coding in the query criteria for the first field it worked. However using the same coding in all of the criterias with the small alterations resulted in the method not working. When I typed data to search the resulting query was blank.

Gurdip Soora

 

[soorags]

I just tried again. The third time I entered data the result was blank, again. I've tried lots. Best to try to get info from library.

Gurdip.

 

[ZOR]

Soorags, this forum is the best on the planet, there are so many people here to help. You have been asked for the SQL of your query, and you show just one line for one field, even though people have been bending over backwards to help. Go into your query design window, where you have entered each fields line of NZ. When you are in design view, select View from the menu selection at the top. Then select SQL View. You will then find a COMPLETE sql statement. Cut and paste that into the forum page and as sure as eggs you will get help.

 

[soorags]

SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address]
FROM [UK Table]
WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));


The above is the SQL coding.

Gurdip.

 

[soorags]

I have put [] brackets around Address, City and Country where required and this seems to work.

 

[soorags]

The query by form method now works. It retrives the required data.

 

[soorags]

It retrieves the required data.

Now I would like the same form to display a table of the results at the bottom. How can I do this?

Gurdip.

 

[TheAceMan1]

How are ya soorags . . .

[blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .

 

[kjv1611]

It retrieves the required data.

Now I would like the same form to display a table of the results at the bottom. How can I do this?

Assuming you are wanting to return your query results at the bottom of your form, you'll need to setup a subform, and then set the record source of that subform to the Query, or to the SQL string, if you prefer to run the "query" in VBA as apposed to an Access Query object.

--

"If to err is human, then I must be some kind of human!" -Me