form-based search

[Lifeseeker]

Hi,

I have three unbound textboxes in total on this search form.

The first two are date fields and they all work well if you leave them empty.

The problem is....if you leave the first two date fields empty and pick a value from the 3rd one(it's a combo box), query returns ALL records. (it's kind of just ignoring the 3rd combo)


However, if all three fields are filled, query returns the right results.

Is this a Null or not Null problem?

 

[Khalid_Afridi]

Hi,
check your query, it should have the criteria for Field1 Or Field2 and Field3...

 

[Lifeseeker]

Hi,
check your query, it should have the criteria for Field1 Or Field2 and Field3...

Below is the query in SQL view: (The WHERE part)


WHERE (((Record.Type_of_Change) Like "*" & [Forms]![Date Search Form]![Type] & "*") AND (([Master Fee].Effective_Date) Between [Forms]![Date Search Form]![From:] And [Forms]![Date Search Form]![To:])) OR ((([Forms]![Date Search Form]![Type]) Is Null) AND (([Forms]![Date Search Form]![From]) Is Null)) OR ((([Forms]![Date Search Form]![To]) Is Null));

Does the order matter here?

The code above seems logical. However, when both the date fields are left blank, the report skips the [type] parameter and returns all records.

It works when all three text boxes have content.

Thoughts/comment much appreciated.

 

[spikepl]

One entire line in the QBE grid is one set of criteria, ie.

WHERE some_criteria1

When you have two lines of criteria in the QBE grid, then ALL the criteria form a new set, thus

WHERE all_criteria_in_line1 OR all_criteria_in_line2 and so on

one line of criteria must contain ALL the clauses pertaining to that set, i.e.
criteria_in_line1 = criterion1 AND criterion2 AND criterion3 ...

You SQL is currently returning the results you ordered it to.