Search Form coding technique question (BuildCriteria)

[Clif McIrvin]

I created my first search form last week; and in today's thread "Using
only one record" Allen Browne offered his sample search form:

Here's an example of how to make a search form:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It involves writing some VBA code to create the Filter string. It's
worth
spending half an hour to download the example and see how it works, as
you
will use this approach in many other contexts as well.

-----------

In the process of crafting my search form, I stumbled across (A2003) the
BuildCriteria function. I notice that Allen's example doesn't use it,
and I can think of at least two possible explanations:

1. Allen's code predates the BuildCriteria function (or)
2. By avoiding BuildCriteria you (the developer) retain control over
precisely what your code is doing.


The question: Should I avoid, or use, BuildCriteria?

 

[Albert D. Kallal]

1. Allen's code predates the BuildCriteria function (or)
2. By avoiding BuildCriteria you (the developer) retain control over
precisely what your code is doing.


The question: Should I avoid, or use, BuildCriteria?

I don't use BuildCriteria that much. There no big reason to use it, or big
reason to avoid it. It really much an issue of coding style, and nothing
more, nothing less.

I mean,

strWhere = "City = 'Edmonton' "

or

strWhere = buildcriteria("City",dbText,"Edmonton")

Looking at the above two, I tend to see the 1st example as more clear, and
more easy to read.

And, I did not have to "know" what dbText is for. In the 1st example, you
building a string and the rules you use will be based on your experience
using sql. So if one types in a bit more sql by hand, then one likely will
use the 1st syntax. (I suspect that just Allen reason here).

There is really no right/wrong answer here..but just one of your coding
style. I just think the 1st example is easier to read...

However, there really no reason to not use buildCritera..and if you like
it..then by all means use it. I do think BuildCriteria() does have some
advantages when you work with dates.

eg:

strWhere = "InvoiceDate = #" & format(dtDateVar,"mm/dd/yyyy") & "#"

or

strWhere = buildcriteria("InvoiceDate",dbdate,dtDateVar)

Remember you have to ALWAYS format your dates in query as usa format, so,
for dates, I think BuildCriteria is cleaner, but then again, in all my
applications, I have a quotes function, and date function that I use, so, I
would go:

strWhere = "InvoiceDate = " & quDate(dtDateVar)

and, my qudate function that I been using for about 10 years follows my sig


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


Public Function qudate(mydate As Variant) As String

' returns a formatted string of date, surrounded with # signs
' format is mm/dd/yyyy as required for sql, regardless of date setting

If IsNull(mydate) = True Then
qudate = ""
Else
qudate = "#" & Format(mydate, "mm/dd/yyyy") & "#"
End If

End Function