multiple criteria but allow null values

[Happy YN]

This must be a common problem but I can't get around it. I need to build a query which has as criteria, textboxes for each field in the criteria. I need all the criteria to be true in other words an AND criteria however if one of the textboxes is empty I need to allow the query to allow all records on that field i.e no criteria. I made a query which has as the top line in the grid the textbox and as the criteria line (fieldname or is null) and it works however I need to make it a like query to allow wildcards and I cannot seem to do it

I also tried to use an iif statement as the criteria
iif(isnull(textbox),,like textbox) but it gets stuck on syntax errors so what is the correct way?
Thanks

 

[jfgambit]

Try:

IIf([Forms]![frmName]![Textbox] Is Null,[NameofField],[Forms]![frmName]![Textbox])

in the criteria of the field.

 

[Happy YN]

Nope!
After fiddling for over an hour, I realize that it is just not responding to my if statement at all.
i.e. it is not using the true or false part of the if statement as a criteria.
Has anyone used an iif statement as a criteria successfully?
Does anyone know a way to use a textbox as a criteria but if it is blank it should not be used.
I have many fields which all have a different textbox as a criteria in an AND scenario

 

[Pat Hartman]

IIf(IsNull([Forms]![frmName]![Textbox]),[NameofField],[Forms]![frmName]![Textbox]) - should work. Make sure that you have changed the names properly.

[Forms]![frmName]![Textbox] - replace frmName with the name of your form and replace Textbox with the name of the control on the form

[NameofField] - replace NameofField with the name of your table column.

This IIf() returns either the value from the form field (when it is not null) or the value from the actual table field when the form field is null. Therefore when the form field is null, the compare should be comparing a field to itself.

 

[Happy YN]

This is my where clause of the sql
WHERE (((tblBook.strAuthor)=IIf(IsNull([forms]![frmbook]![txtandauthor]),[strauthor],(tblBook.strAuthor) Like [forms]![frmbook]![txtandauthor])))
It returns one completely empty record???
The textbox has el* as a value
Thanks

 

[Happy YN]

some progress using
WHERE (((tblBook.strAuthor) Like IIf(IsNull([forms]![frmbook]![txtandauthor]),[strauthor],[forms]![frmbook]![txtandauthor])))

i.e. moved the "like" but still not getting null records?

tried inserting is null but now it returns no records? i.e.
WHERE (((tblBook.strAuthor) Like IIf(IsNull([forms]![frmbook]![txtandauthor]),((tblBook.strAuthor)=[strauthor] Or (tblBook.strAuthor) Is Null),[forms]![frmbook]![txtandauthor])))

 

[Fornatian]

Wouldn't Nz([Forms]![frmName]![Textbox]),[NameofFie
ld]) work equally well?

 

[Happy YN]

no 1 for some reason nz(forms!Formname!txtname) returns no records even when something is typed in the textbox
also it does not solve the null problem and I wonder if like will still work
SO still wondering how to incorporate the records which have no value (null) in the iif statement??
Thanks

 

[Pat Hartman]

Have we got all the requirements yet?

WHERE tblBook.strAuthor Like IIf(IsNull([forms]![frmbook]![txtandauthor]),tbl
Book.strAuthor,[forms]![frmbook]![txtandauthor]) Or tblBook.strAuthor Is Null;

The IIf() has three components, the condition, the true result, and the false result. The true result is returned if the condition is true and the false result is returned if the condition is false. It makes no sense to have an or in either of these parts.

The IIf() does support nesting so that either the true result or the false result can themselves be II() functions. And the condition can be compound. So the following are possible:

IIf(cond1 OR cond2, IIf(...), false result)
IIf(cond1 AND (cond2 OR cond3), IIf(...), IIf(...))
etc.

 

[Happy YN]

Pat if I put Or at the end t will always return the null vales as well as the if values.
i only want the null if the if is false . at the moment it is returning all the records of the specifed field which is what I want but it is not returning the records which have null in that field
I appreciate you have replied twice tonight but I wish I could pierce this fog!
Thanks

PS even if I just put Is Null in the true part of the if , I get no records even though there are over 4000!

I don't see how nesting would help. I only have one if in my mind i.e.
if the textbox is empty ,show all records-including those with a blank entry for this field,otherwise match the text -and allow wildcards

Thanks again

 

[Jon K]

"..... if the textbox is empty, show all records-including those with a blank entry for this field, otherwise match the text - and allow wildcards"


Attached is a small DB. You can open the form, enter some criteria or leave the text boxes blank, and click on the command button to view the records.

The following is the query that I used (though Access may add some extra brackets and the default <>False in the Where Clause):-

SELECT *
FROM tblBook
WHERE IIf(IsNull(Forms!frmBook!txtAuthor), True, Author Like
Forms!frmBook!txtAuthor) And
IIf(IsNull(Forms!frmBook!txtPublisher), True, Publisher Like
Forms!frmBook!txtPublisher);


When the condition is true (i.e. when a text box is left blank), True will return every record including null values. When the condition is untrue, Like allows the using of wildcards.

Notes:
(1) If you build the criteria in the query grid, type each IIf() expression in the Field: cell and put <>False in the Criteria: cell.

(2) If you want to exclude null values, replace True with the expression FieldName=FieldName.

Hope it helps.


The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[Happy YN]

Thanks jon . will look at it soon.
In the meantime I used a union query to join the query which brought out the wildcards but did not allow nulls , and another qury which allowed nulls but not widcards! The result is good but its a bit clumsy . I will post it later but if yours works I will use it
Thanks everyone for their help
I am suprised such an issue has not come up before??!
Happy YN

 

[Happy YN]

yes Jon! That works perfectly Thanks a million!
I am just wondering if I can take this a step further. Suppose i would have another text box into which I would sometimes type a string. How could I query whether that string comes up in any of the fields
e.g. find records whose author field matches a* AND where any field in that records contains ven*.
If the textbox would be empty it would let thru all the records (inc NULL) but if there is text there it would be implied that the search is for that string in any of the fields

Thanks again

 

[Jon K]

To find records whose author field matches a* AND where any field in that records contains ven*, you can adjust the Where Clause and add some OR expressions in a pair of brackets.


Assuming ven* is contained in a text box txtSearchString, the query would be:-

SELECT *
FROM tblBook
WHERE IIf(IsNull(Forms!frmBook!txtAuthor), True, Author Like
Forms!frmBook!txtAuthor) AND
IIF(IsNull(Forms!frmBook!txtSearchString), True,
(Field1 Like Forms!frmBook!txtSearchString
OR Field2 Like Forms!frmBook!txtSearchString
OR Field3 Like Forms!frmBook!txtSearchString
OR Field4 Like Forms!frmBook!txtSearchString));


As the Where Clause now contains AND and OR, all references to txtSearchString must be put inside a pair of brackets.

 

[Happy YN]

That sounds good jon. Just one more point .Can't I somehow search all fields using the * instead of field 1 field2 etc
Thanks again you've helped immensely
an even happier Happy YN

 

[Jon K]

The answer is No. * cannot be used in the criteria i.e. the Where Clause.

 

[Happy YN]

Thanks for this jon. i expect ALL is also not an option. What about defining a variable which holds all those fields?
This is now purely academical because you have done more than enough for me!
Thanks again a million

 

[Jon K]

Yes. "ALL" is also not an option.

To use variables, you still have to build the string in the format "Field1 Like SearchString OR field2 Like SearchString", etc. Query syntax is rather rigid. There are no shortcut methods.


As an illustration of using variables, I have attached another small DB. When the form is opened, the tables in the DB are listed in the first list box. (Code in the On Open Event of the form)

When you click on a table in the first list box, all the text and the memo fields of that table will be listed in the second list box. (Code in the On Click Event of the first list box)

You can then select the fields, type a search string with wildcard, and click on the Search button to view the records from the selected table. The query created by the command button is also displayed in a text box for easy viewing. (Code in the On Click Event of the command button)


Notes:
Limitations: In the code, the double quote " is used as the delimiting character in the search string. Trying to search for a string containing " will produce a syntax error.

The DB was written in Access 97, so DAO was used. If you write the code in Access 2000 or 2002, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select the Microsoft DAO Object Library 3.6).

 

[Happy YN]

Thanks jon, will certainly take a look at that.
I have however a new problem with this sql string that you have helped me create. It works marvellously but I have gotten stuck on a relatively minor issue.
briefly , till now when a user deleted a record , it was not really deleted, rather it caused a string ("deleted" & Date()) to be entered into a field . that way all the queries were told to find records which match criteria as long as deleted* was not in that specific field (WHERE [fieldname] not LIKE deleted* OR is null) This allowed the user to enter deleted* into the "any" (on all fields) search which would pick up deleted entries and worked wonderfully.
However now that we have combined the search criteria and the any search into one sql, I am stuck.
I don't want the user to pick up deleted records when doing a normal search for text in particular fields but I still want them to be able to type deleted* into the any textbox and the deleted records should be allowed thru. This however is a contradiction in the where clause. Either I am instructing thatthe deleted records should be blocked , on the other hand I want to be able to pick them up in the any search textbox

Here is my current sql
SELECT [tblBook].[lngBookNo] AS Valmadonna, [tblBook].[strBookName] AS BookName, [tblBook].[strBookNameHebMapped] AS [Hebrew Name], [tblBook].[strDateInNums] AS PrintDate, [tblBook].[strAuthor] AS author, [tblPrinter].[strPrinter] AS Printer, [tblType].[strType] AS Type, [tblBook].[dateStamp], [tblBook].[strDateHebMapped] AS hebdate, [tblBook].[strBibliography], [tblBook].[strNotes], [tblBook].[strSizeMapped], [tblBook].[mmoInfo], [tblCity].[strCity] AS city, [tblBook].[strDatAcronymMapped]
FROM tblCity INNER JOIN (tblType INNER JOIN (tblPrinter INNER JOIN tblBook ON [tblPrinter].[ID]=[tblBook].[tblPrinter_ID]) ON [tblType].[ID]=[tblBook].[tblType_ID]) ON [tblCity].[ID]=[tblBook].[tblCity_ID]
WHERE (((IIf(IsNull([forms]![frmbook]![txtandauthor]),True,[tblbook].[strauthor] Like [forms]![frmbook]![txtandauthor]))<>False) And ((IIf(IsNull([forms]![frmbook]![txtandtitle]),True,[tblbook].[strbookname] Like [forms]![frmbook]![txtandtitle]))<>False) And ((IIf(IsNull([forms]![frmbook]![txtandval]),True,[tblbook].[lngbookno] Like [forms]![frmbook]![txtandval]))<>False) And ((IIf(IsNull([forms]![frmbook]![txtandprinter]),True,[tblprinter].[strprinter] Like [forms]![frmbook]![txtandprinter]))<>False) And ((IIf(IsNull([forms]![frmbook]![txtandcity]),True,[tblcity].[strcity] Like [forms]![frmbook]![txtandcity]))<>False) And ((IIf(IsNull([forms]![frmbook]![txtandDate]),True,[tblbook].[strDate] Like [forms]![frmbook]![txtandcity]))<>False) And ((IIf(IsNull([forms]![frmbook]![txtandType]),True,[tbltype].[strType] Like [forms]![frmbook]![txtandType]))<>False) And ((IIf(IsNull([Forms]![frmBook]![txtAndAny]),True,(([tblbook].[lngbookno] Like ([Forms]![frmBook]![txtAnd Any])) Or ([tblbook].[strbookname] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblPrinter].[strprinter] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblcity].[strcity] Like ([Forms]![frmBook]![txtAndAny])) Or ([tbltype].[strtype] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[mmoInfo] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[lngbookid] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[mmoaddnotes] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[strbibliography] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[strnotes] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[strsizemapped] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[datestamp] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[strauthor] Like ([Forms]![frmBook]![txtAndAny])) Or ([tblbook].[strdate] Like ([Forms]![frmBook]![txtAndany])))))<>False))
ORDER BY [tblbook].[lngbookno];


By the way each time I switch to design view and do an adjustment it tells me that there is a syntax error but if I adjust in sql view it will save it fine and work OK
can you spot any obvious errors in this forest?

Thanks again for all your terrific help

 

[Jon K]

To avoid the contradiction, you can use a Check Box on the form for the deleted records.

Name the Check Box as chkExcludeDeleted, set its Default Value to True, and change the Caption of its label to Exclude Deleted Records. In the query, add a reference to the check box in the AND part of the Where Clause. Assuming the table field for the deleted records is strDeletedDate, the SQL statement of the query in my example would be:-

SELECT *
FROM tblBook
WHERE IIf(IsNull(Forms!frmBook!txtAuthor), True, Author Like
Forms!frmBook!txtAuthor) AND
IIF(Forms!frmBook!chkExcludeDeleted=True, IsNull(strDeletedDate), True) AND
IIF(IsNull(Forms!frmBook!txtSearchString), True,
(Field1 Like Forms!frmBook!txtSearchString
OR Field2 Like Forms!frmBook!txtSearchString
OR Field3 Like Forms!frmBook!txtSearchString
OR Field4 Like Forms!frmBook!txtSearchString));


This way, the deleted records will not be picked up during a normal search.


As for the syntax error when adjusting the query in Design View, probably Access was confused by the extra () and [] brackets that it added. Another reason might be due to the fact that the OR part of the expression exceeded a Field: cell's 512 character limit. For complicated criteria, it would be easier to directly type the Where Clause in SQL View, as you don't need to type the extra brackets and there is no 512 length limit.