HI

I have a form set up in which the user inputs various searh terms which are linked to queries and the results are presented on a report.

As there aer 7 search input areas, and sometimes these are blank, the search can take up to 30sec- 1min to return (yes its is a large database!)

Upon hitting the "search all " comannd , im presuming that it is doing a search regardless of the blank input.


I would like to speed this up and i thought that if i could get the form to not run the query if there is no entry that this would help?

Soooo, how do i get it to ignore a Null entry?

thanks in advance!

Please post the code 'behind' that <search all> command button. We can't tell what it's supposed to do if you don't tell us the code.

One approach to doing what you describe would be to have code behind that button that dynamically builds a SQL-statement, leaving out WHERE clauses for the controls that have no entries.

NOTE: if someone types something into a control, then deletes it and moves on, I'm under the impression that Access no longer treats that as Null. Better check it out!

Thanks for you reply.

The 'search all' is opening another report full of subreports , linked to queries!

An example of a query criteria is

Like "*" & [Forms]![MasterCOMBINEDSearchForm]![txtRego] & "*"


Where txtRego is obtained from the search form. Im figuring i need something like where txtRego is null, but coding is not my strength!

Cheers

The quick and dirty is to change all instances of

Like "*" & [Forms]![MasterCOMBINEDSearchForm]![txtRego] & "*"

to

=[Forms]![MasterCOMBINEDSearchForm]![txtRego] OR ([Forms]![MasterCOMBINEDSearchForm]![txtRego] & "" = "")

If the table fields are properly indexed this query will use those indexes, and be MUCH faster.

A better solution is to use VBA code to loop through the MasterCOMBINEDSearchForm's controls and build a SQL string for all the non-blank entries. Post back if you need to do it this way.

Excellent thankyou VERY MUCH

Wasnt working at first for some reason, then it suddenly did!

Works a lot faster now, even when converting it back to a Contains type search

I have all my fields indexed that im using for searching

cheers

Note that your LIKE "*" & <fitb> & "*" search will NOT be able to use any indexing on the field. Indexing works on exact matches, or on "first character" matches like

LIKE field = <parameter> & "*"

Having a leading wildcard makes indexes useless.