Ruling out combinations without if statements?

[Guest]

Is there a way to rule out combinations with out having to use an if then
block.

My form has 5 different text boxes. I want the user to enter the information
they wish to search by and have which ever text boxes that are not null have
that information passed to an SQL statement to search by.

The problem is, if I do an if then block for

txtdate
txtPIN
txtSN
txtTicketNumber
txtStaff

there's 125 possible combination of null vs not null within those 5 text
boxes. Is there any way to make an SQL statement that does something like

SELECT table.*
FROM table
WHERE field LIKE txtDate IF textbox NOT NULL AND field LIKE txtPIN....

instead of doing an if statement and SQL statement for each combination.

Is there a completely different way to do this and I'm just missing it?

-Thanks in advance.

 

[mcescher]

Use the the star wildcard

WHERE field LIKE txtDate & "*"

Also, make sure you want to use AND. I'm guessing you might want an
OR between your fields.

HTH,
Chris M.

 

[Guest]

mcesher's approach will work (sortof) in that you may get some records you
don't want.
You need not to worry about the number of combinations. Here is an approach
I use in a case like this. Create a function that returns a string based on
the values in th text boxes:

Private Function BuildWhere() As String
Dim strWhere As String

If Not IsNull(Me.txtdate) Then
strWhere = "[SomeDateField] = '" & Me.txtdate & "'"
End If

If Not IsNull(Me.txtPIN) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[PIN] = '" & Me.txtPIN & "'"
End If

Then just continue this code for each of the fields you want to include.

 

[Steve]

Here is maybe a simpler approach than the others:
In your query, put the following criteria in the date field:
Forms!nameOfYourForm!TxtDate Or (Forms!nameOfYourForm!TxtDate Is Null)

Do the criteria for the other four fields the same.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Albert D. Kallal]

Don't use if then else...

Write you code so that each field that has a value is CUMULATIVE added to
the conditions.

Here is how:

As you are finding out, 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 ignore.

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.

So, for each new condition, you check strWhere.....and add a "and"...this
make it cumulative.....

If the control is blank..the condition and filter is skipped...

 

[Guest]

I use a variation of Dave's method. Instead of defining strWhere, I use a
variant to reduce the amount of code. The segment of code:

varWhere = (varWhere + " AND ")

Will only add the " AND " segment if varWhere already has something in it.
By declaring varWhere and BuildWhere as variants, I can use them as the
criteria in one of the domain functions DMAX(), DMIN(), DLOOKUP() or I can
build a sql string something like:

strSQL = "SELECT * FROM myTable " & (" WHERE " + BuildWhere()) _
& "ORDER BY ...."

Because I am returning a variant, if none of my text boxes or combo boxes
have an entry, BuildWhere will return a NULL value, and the WHERE clause in
the line above will be ignored entirely.

HTH
Dale


Private Function BuildWhere() As Variant

Dim varWhere As Variant
varWhere = NULL

If Not IsNull(Me.txtdate) Then
varWhere = "[SomeDateField] = '" & Me.txtdate & "'"
End If

If Not IsNull(Me.txtPIN) Then
varWhere = (varWhere + " AND ") & "[PIN] = '" & Me.txtPIN & "'"
End If

'This one adds a constraint that has a numeric field as the bound
'column of a combo box.
If Not isnull(me.cbo_Something) then
varWhere = (varWhere + " AND ") & "[SomeField] = " &
me.cbo_Something
endif

BuildWhere = varWhere

End Function
--
Email address is not valid.
Please reply to newsgroup only.

mcesher's approach will work (sortof) in that you may get some records you
don't want.
You need not to worry about the number of combinations. Here is an approach
I use in a case like this. Create a function that returns a string based on
the values in th text boxes:

Private Function BuildWhere() As String
Dim strWhere As String

If Not IsNull(Me.txtdate) Then
strWhere = "[SomeDateField] = '" & Me.txtdate & "'"
End If

If Not IsNull(Me.txtPIN) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = "[PIN] = '" & Me.txtPIN & "'"
End If

Then just continue this code for each of the fields you want to include.

--
Dave Hargis, Microsoft Access MVP

Is there a way to rule out combinations with out having to use an if then
block.

My form has 5 different text boxes. I want the user to enter the information
they wish to search by and have which ever text boxes that are not null have
that information passed to an SQL statement to search by.

The problem is, if I do an if then block for

txtdate
txtPIN
txtSN
txtTicketNumber
txtStaff

there's 125 possible combination of null vs not null within those 5 text
boxes. Is there any way to make an SQL statement that does something like

SELECT table.*
FROM table
WHERE field LIKE txtDate IF textbox NOT NULL AND field LIKE txtPIN....

instead of doing an if statement and SQL statement for each combination.

Is there a completely different way to do this and I'm just missing it?

-Thanks in advance.