I have a form that is used as a search form. When used it opens a form and diplays the result in a filtered set of records. I set up a query to serve as the filter that is used. So far so good. Here is the problem part of the code I have in the "where" clause:
((Main.[PRIMARY PHONE]) Like [forms]![frmfilterforrecord]![CW_PrimPhone] & "*")
I have several unbound text boxes used on the search form. The idea was to set up a simple way to search using any or all textboxes filled in. The problem is that if say the [CW_PrimPhone] is left blank and the field is null in a record it won't return those particular records.
Does anyone have any ideas how to alter my code so that it will only search using the textboxes filled in on the search form? Or maybe that it will return records with a null value. I know that the * is the problem, but don't know what I need to replace it.
I'm now having a performance issue with this same code. It works, but is very slow. Part of the problem is that is searching about 600,000 records. The DB Backend is on SQL Server, otherwise the db probably wouldn't run at all
Part of the problem is that SQL Server does not recognize the IIf() formula, so it has to transfer all the records across the network so Access can do it. You might look at a stored procedure instead of this query (so SQL Server can do it all), or build SQL in code based on what the user has filled out or left blank, then make that SQL the source of the form/subform (so you don't have anything in the SQL that SQL Server can't process).
This method may work better (no IIf formula), but gets messy with too many different controls, which it sounds like you have:
Well to clarify, Access (Jet) SQL does recognize IIf(), SQL Server (T-SQL) does not. Its equivalent on SQL Server is the CASE statement.
When you start getting into a number of controls that may or may not be filled out (typical of search forms), I prefer to build SQL in code. It sounds like what you're talking about is the wherecondition argument of DoCmd.OpenForm. It could solve your problem. I typically have the search controls on a main form, and the results in a subform. When the user clicks a "search" button, I build SQL based on what they input, then set the subform's RecordSource to that SQL.
It's getting a little more clear for me, but could you explain a little more. I haven't done much with SQL and am not sure exactly what you mean. Are saying build code in the access side or on the SQL side? If on the Access side, do you just substitute T-SQL code instead of what I used or .....