> "Emily T" <EmilyT[ at ]discussions.microsoft.com> wrote in message
> news:9575CC62-C496-4B56-B5AC-4F305636186E[ at ]microsoft.com...
>
> For performance reasonse, make sure there is an index on the several fields
> that you plan to seach on.
>
> Next, remove ALL critera from the actual query...espically if your using
> expresions to check for blank values.
>
> Then, build the seach critera in code and pass it to the reprot as an
> "where" clause.
>
> > BUT then changes
> > his/her mind and clicks on the "country" option button to search by
> > counry, I
> > want the "customer number" value to be cleared.
>
> Use the county text box on enter event to clear out the customer number
>
> eg:
>
> me.CustomerNumber = null
>
> The following info will give you an idea about how to make a "where" clase
> in code....
>
> Putting forms! expression in queries can get really messy real fast.
>
> Even worse, is now that the query is now "married" and attached to that ONE
> form. Often, I have a nice query that I could use MANY times for different
> reports, and often even that same query could be used for reports...but then
> someone comes along and puts in a expression that means the query is ONLY
> good when that form is opened.
>
> Worse, is very hard to control things like having 5 combo boxes, but the
> user only selects restrictions in 3 of the combo boxes...and wants the other
> 2 to be ignored in the criteria.
>
> I could probably write another 10 or pages as to why putting forms
> expressions in queries is bad (besides...it makes the queries real ugly, and
> hard to read. and, the sql then is not standard anymore (it will not work
> with server based systems either).
>
> So, the solution use now is simply to take the values from the form, and
> build your own where clause in code. That way, you simply design the reports
> (or forms), and attached them to the query, BUT NO FORMS! conditions are
> placed in the query.
>
> To "send" the conditions to the report (or form), you simply use the "where"
> clause. This is exactly why ms-access has this feature...and it solves a
> zillion problems...and will reduce your development costs by a substantial
> amount.
>
> Take a look at the following screen shots to see what I mean:
>
>
http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html>
> The code to make those above screens work and launch the report with the
> selected restrictions when you hit the "print" button is easy:
>
>
> dim strWhere as string
>
> ' select sales rep combo
>
> if isnull(cboSalesRep) = false then
>
> strWhere = "SalesRep = '" & cboSalesRep & "'"
>
> end if
>
> ' select what City for the report
>
> if isnull(cboCity) = false then
> if strWhere <> "" then
> strWhere = strWhere " and "
> endif
> strWhere = strWhere & "City = '" & cobCity & "'"
> end if
>
> Note how the 2nd combo test is setup. You can add as "many" more conditions
> you want. Lets say we have a check box to only include Special Customers. We
> can add to our very nice prompt screen a check box to
>
> [x] Show Only Special customers
>
> The code we add would be:
>
> if chkSpeicalOnly = True then
> if strWhere <> "" then
> strWhere = strWhere " and "
> endif
> strWhere = strWhere & "SpecialCust = true"
> endif
>
> For sure, each combo and control we add to the nice report screen takes a
> bit of code, but no more messy then the query builder..and this way, each
> query is nice and clean, and free of a bunch of HIGHLY un-maintainable
> forms! expressions.
>
> Further, it means you can re-use the same query for different reports, and
> have no worries about some form that is supposed to be open. So, a tiny bit
> more code eliminates the messy query problem.. For me, this is very worth
> while trade.
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal[ at ]msn.com
>
>
>