By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

Issues with Search Form = IIF Statements

P: n/a
I'm having a brain blockage day....

Scenario:

Search Form with 6 text boxes
Query has same six fields
Each has this IIF: IIf(IsNull([Forms]![CashItems1]![District]),"",[Forms]![CashItems1]![District])
with each dictating the correct text box of course.

SQL dictates... If text box 1 isNull do nothing or do this OR if text
box 2 isNull do nothing or do this... etc... works fine if I only
enter search criteria into one of the six fields on the form.... but
when I try to enter criteria into 2 or more... its hosed. if I use
AND... they'll all need to have criteria entered. They would like it
so if they happen to have 3 pieces of information to look up a Members
Charge-offs they'll be able to.

Did I make sense? Would greatly appreciate any help...

Christa.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
mi************@yahoo.com (misschristalee) wrote in message news:<5c**************************@posting.google. com>...
I'm having a brain blockage day....

Scenario:

Search Form with 6 text boxes
Query has same six fields
Each has this IIF: IIf(IsNull([Forms]![CashItems1]![District]),"",[Forms]![CashItems1]![District])
with each dictating the correct text box of course.

SQL dictates... If text box 1 isNull do nothing or do this OR if text
box 2 isNull do nothing or do this... etc... works fine if I only
enter search criteria into one of the six fields on the form.... but
when I try to enter criteria into 2 or more... its hosed. if I use
AND... they'll all need to have criteria entered. They would like it
so if they happen to have 3 pieces of information to look up a Members
Charge-offs they'll be able to.

Did I make sense? Would greatly appreciate any help...

Christa.


I have a search form with many search fields. In order to build the
SQL string I need to have a criterion for the first field no matter
what so that there's always a WHERE and something following it. The
first field is a text field so I use (in form code):

If Not IsNull(txtDistrict.Value) Then
strWhere = " WHERE [District] LIKE '" & txtDistrict.Value & "'"
Else
strWhere = " WHERE (([District] LIKE '*') OR ([District] Is Null))"
End If

The second strWhere simply returns everything. So your IIF statements
could look something like:

YourQuery.SQL = "SELECT * FROM tblSearch " &
"IIf(IsNull(Forms!CashItems1!District.Value), 'WHERE (([District] LIKE
'*') OR ([District] Is Null))', 'WHERE [District] LIKE '" &
Nz(Forms!CashItems1!District.Value,"") & "')" &
IIf(IsNull(Forms!CashItems1!County.Value), '', ' AND [County] LIKE '"
& Nz(Forms!CashItems1!County.Value, "") & "')"

and so on for the six search fields. Note: I have not tested the
string above. It's not very pretty but it fits in with the way you
are trying to do things. I don't know if the Nz's are needed on the
Not IsNull side of the IIf but it doesn't hurt to have them.

James A. Fortune
Nov 13 '05 #2

P: n/a
Hi Christa,

I'd suggest building an SQL string in code, where you can append as many
"AND's" as you need.
The other thing that I like to do is to display the result in a
datasheet-style subform ("sbfCustomers" in the example below), rather than
having to open an external query.

*Notes:
You will have to edit this code to include your control names, etc.
The following code from my form uses two combo-boxes to filter the data. I
use combo-boxes so that I can select from *existing* values.

Here is the SQL used as the Row Source of the 2 combo-boxes (which in this
case are purposely named the same as the fields that I want to search ...
"FirstName" and "LastName")
These combo-boxes both also use a UNION SELECT statement to add "(All)" to
their RowSource... http://www.mvps.org/access/forms/frm0043.htm
SELECT DISTINCT tblCustomers.FirstName From tblCustomers UNION Select
"(All)" as Bogus FROM tblCustomers;
SELECT DISTINCT tblCustomers.LastName From tblCustomers UNION Select
"(All)" as Bogus FROM tblCustomers;
(Using the "SELECT DISTINCT" as above in the combo-box's RowSource
eliminates duplication of any of the names)

You call this procedure from each of your combo-box's AfterUpdate event. The
easiest way is to just type "= fRequerySubform()" in each of the combo-boxes
"AfterUpdate" field directly in their respective property sheets.

The beauty of this approach is it's flexibility;
Null - valued combo-boxes (or combo-boxes with "(All)" selected) are
ignored, and are not appended to the WHERE portion of the SQL string.
If only one combo-box "qualifies", the "AND" is dropped.
If NO combo-boxes are specifying criteria, ALL records are returned.
All results are displayed immediately ... right in the subform!

Here is the (tested and working) code sample:
**************************************************
Option Compare Database
Option Explicit

Public Function fRequerySubform()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.

Dim MySQL As String
Dim whr As String

Dim Msg As String
Dim CR As String
CR = vbCrLf

Dim ctl As Control

'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT tblCustomers.* "
MySQL = MySQL & "FROM tblCustomers "

'Build the WHERE portion
whr = "" 'initialize variable

For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If Not IsNull(ctl) And InStr(1, ctl, "(All)") = 0 Then
whr = whr & "(tblCustomers."
whr = whr & ctl.Name
whr = whr & " = "
whr = whr & Chr$(39) & ctl & Chr$(39) 'This assumes that "ctl"
contains Textual data. You may need to edit this if not Text.
whr = whr & " ) AND "
End If
End If
Next ctl

If Len(whr) > 0 Then
'Lose the last 5 characters (" AND ")
whr = Left$(whr, Len(whr) - 5)
'Insert the WHERE (If there is one) into the SQL statement...
MySQL = MySQL & "WHERE (" & whr & ") " & CR
End If

MySQL = MySQL & "ORDER BY tblCustomers.LastName, tblCustomers.FirstName "
MySQL = MySQL & ";"

'Debug.Print MySQL
Me![sbfCustomers].Form.RecordSource = MySQL

End Function

*****************************************
Here are 2 samples of the SQL that is generated by this function, and is
used as the subform's Recordsource:

SELECT tblCustomers.* FROM tblCustomers WHERE ((tblCustomers.FirstName =
'Don' ))
ORDER BY tblCustomers.LastName, tblCustomers.FirstName ;

SELECT tblCustomers.* FROM tblCustomers WHERE ((tblCustomers.LastName =
'Leverton' ) AND (tblCustomers.FirstName = 'Don' ))
ORDER BY tblCustomers.LastName, tblCustomers.FirstName ;
--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================


"misschristalee" <mi************@yahoo.com> wrote in message
news:5c**************************@posting.google.c om...
I'm having a brain blockage day....

Scenario:

Search Form with 6 text boxes
Query has same six fields
Each has this IIF: IIf(IsNull([Forms]![CashItems1]![District]),"",[Forms]![CashItems1]![Distric
t]) with each dictating the correct text box of course.

SQL dictates... If text box 1 isNull do nothing or do this OR if text
box 2 isNull do nothing or do this... etc... works fine if I only
enter search criteria into one of the six fields on the form.... but
when I try to enter criteria into 2 or more... its hosed. if I use
AND... they'll all need to have criteria entered. They would like it
so if they happen to have 3 pieces of information to look up a Members
Charge-offs they'll be able to.

Did I make sense? Would greatly appreciate any help...

Christa.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.