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.
