searching on multiple criteria problem

Jimmythemadman

Registered User.
Local time
Today, 17:03
Joined
Jun 18, 2007
Messages
12
i have created a form for my database where i intend to give the user the option of searhing my more than one criteria but not all.
i have used a system of combo boxes for the user to enter thier choises leaving the combobox blank for any criteria they dont know.
the user then clicks th button to open the query

in the query i have set the criteria for the fields to equall the related combo bo in the form.

however if the comboboy is left blank (0 or null) the query looks for a record where that field is also null. as a result it wont return any records.

an example

on the table tblFault there is the record

FaultID; 7
Computer id; 2
Name; c
Room-ID; it1
Date Of Fault; 05/06/07

on the form the user enters the following values into the combobox

FaultID; 7
ComputerID;
Nme; c
Room-ID; it1
Date of Fault 05/06/07

the query returns no records insead of the record above.
this is because it is looking for a record in the table where the Computer-ID field contains a null value.

is ther a way to ignore the chriteria if the combobox contains a null value????

i have tried to use vb to convert all null values to be "Like "*"" but it keeps returning a type missmatch error but still changes to combo box value to ; Like .
 
i have created a form for my database where i intend to give the user the option of searhing my more than one criteria but not all.
i have used a system of combo boxes for the user to enter thier choises leaving the combobox blank for any criteria they dont know.
the user then clicks th button to open the query

in the query i have set the criteria for the fields to equall the related combo bo in the form.

however if the comboboy is left blank (0 or null) the query looks for a record where that field is also null. as a result it wont return any records.

an example

on the table tblFault there is the record

FaultID; 7
Computer id; 2
Name; c
Room-ID; it1
Date Of Fault; 05/06/07

on the form the user enters the following values into the combobox

FaultID; 7
ComputerID;
Nme; c
Room-ID; it1
Date of Fault 05/06/07

the query returns no records insead of the record above.
this is because it is looking for a record in the table where the Computer-ID field contains a null value.

is ther a way to ignore the chriteria if the combobox contains a null value????

i have tried to use vb to convert all null values to be "Like "*"" but it keeps returning a type missmatch error but still changes to combo box value to ; Like .
 
Assuming the comboboxes are unbound,

go into your query the form is based on,

In the criteria for each searchable field enter the following .


[Forms]![YourFormname]![YourComboBoxName]

eg, for faultID, you want the user to choose from Combobox1 on Form name TEST. in the criteria for FaultID would be.

[Forms]![TEST]![Combobox1]

Ensure you code in to refresh the data after making a selection within the form.

Hope this helps

Kempes
 
I would create the sql string via code...

:)
 
You could try:

Code:
Function SearchCriteria()
Dim SearchFlag as Byte (or Boolean)
            
  With CodeContextObject
    SearchFlag = False
    if IsNumeric(.[FaultID]) then
      SearchCriteria "...
      SearchFlag= True
    if IsNumeric(.[Computer ID]) and SearchFlag = True then
      SearchCriteria = SearchCriteria & " And ...
    Elseif IsNumeric(.[Computer ID] and SearchFlag = False then
      SearchCriteria = " ..
      SearchFlag = True etc

The SearchFlag simply indicates that there is existing Criteria " And " is required.
All you doing is creating a string but first test if the Field is populated or not. The Later simply ignores the field. The String fields you can test with Not IsNull.

Simon
 
Allen brown has a nice piece of code that does a compounding search like you are talking about. You can download a Sample and adjust it to fit your fields here. http://allenbrowne.com/bin/Search2000.zip

This is a great place to start. It shows you how to have search criteria that can handle partial values i.e. Last name: ram will return both Ramirez and Ramon and Tramble
 

Users who are viewing this thread

Back
Top Bottom