tooooooooo many conditions

[Guest]

Hi all,
I have a continous form,in its header there are 5 txt boxes that are used
to search for certain information to be displayed in the details section.
These 5 boxes are : "receiving date","customer","ID","Address","SL"
the user of this database can fill only one text box and clicks on the
button "search" to display the result in the details section.the user can
fill one or more that one box to display more detailed info.
To carry out the function of the button "search" i have to write 10s of
codes to include all conditions,ex. if user fills the box "receiving
date",or the box "customer" plus "address" or may fill the 5 boxes,is there
an easier way to carry out the function of this button without all these
conditions ?
Regards

 

[Guest]

If you test for each of the 5 controls being OR IS NULL you can do it all
with one expression. You don't say how you are restricting the record's so
I'll assume its by setting the form's Filter property. Whichever way you do
it, however, the expression is the same. For the sake of this example I'll
assume that all the fields are text data type apart Receiving Date and ID
which I'll assume are date/time and number data types respectively, so the
code would be something like this:

Dim strFilter As String

strFilter = _
"([Receiving Date] = #" & Format(Nz(Me.[Receiving Date],0),"mm/dd/yyyy")
& _
" OR Me.[Receiving Date] IS NULL) AND" & _
" (Customer = """ & Me.Customer & """ OR Me.Customer IS NULL) AND" & _
" (ID = " & Me.ID & " OR Me.ID IS NULL) AND" & _
" (Address = """ & Me.Address & """ OR Me.Address IS NULL) AND" & _
" (SL = """ & Me.SL & """ OR Me.SL IS NULL)"

Me.Filter = strFilter
Me.FilterOn = True

Ken Sheridan
Stafford, England

 

[Albert D. Kallal]

You only have 5 text boxes?

The following approach should work well for 4, or 20 text boxes:

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 above code (2nd if/end) block is written. you can add 1, or 20
more just like that. Each new condition simply appends to the already
existing. So, for 5 text boxes as you have, really not a lot of code....