Null Values in a multi criteria query

[randaj]

I need to setup a query that has multiple statements but that ignore a statement if no value is entered.

For example; I want to query all my store for people with the last name JOHNSON and who are in STOCKING and who work in LOS ANGELES. I know how to set that up, but what happens if one day I just want LAST NAME and LOS ANGELES and dont care what department they are in (although I would also the department value displayed)?

I would like to have just one query setup that will do this so that the clients can just press a button and enter values as the parameters are displayed.

 

[mas5]

Randaj -

I have done this kind of thing using Visual Basic and also using a filter. I'm not sure what your situation is, but here is an example of what I've done using visual basic. You basically need to build your select statement after the users have made their entries. Below see an example in Visual Basic. I haven't tested this out exactly so my syntax may not be perfect (I've had problems with quotation marks occasionally).

Set RST = New ADODB.Recordset
RST.ActiveConnection = CurrentProject.Connection
RST.CursorType = adOpenStatic
RST.LockType = adLockOptimistic
firstitem = false
strsql = "Select lastname, location, department from
your table where "
if not isnul(lastnameselected) then
strsql = strsql & "lastname = " &
lastnameselected
firstitem = true
end if
if not isnull(deptselected) then
if firstitem = true then
strsql = strsql & " and "
else
firstitem = true
end if
strsql = strsql & "department = " & deptselected
end if
if not isnull(locateselected) then
if firstitem = true then
strsql = strsql & " and "
end if
strsql = strsql & "location = " & locateselected
end if

rst.open strsql

I hope this helps. Are you displaying your data on a form or a report? I have used this method to build filters for reports when a user makes a request.

I have never tried this in a form but you may be able to take that strsql statement (forget all that other code) and set me.recordsource = strsql at runtime and that will run the sql that you have built.

Good luck! I hope this makes sense.

- MAS

 

[scriverb]

I think what you want is multiple criteria expressions that if the user enters data for all, any, or none will either select on the entry if made otherwise return all records for that particular column.

Here is a criteria statement for a Last_Name field:
IIf(IsNull([Enter Last Name:]),[YourTable]![Last_Name],[Enter Last Name:])

If the user enters a Last Name selection of Johnson then only Johnson's will be returned but if nothing is entered then all records are returned as there was no criteria statement at all.

Is this what you were looking for? Bob Scriver

 

[randaj]

Exactly. Thanks.

 

[randaj]

2 more things along the same line. I made a form so that users could select the parameters (combo boxes) instead of having to enter the parameters as they popup. The two I am having issues with are;
1:I would like to setup a parameter with a wild card. I tried this
IIf(IsNull([forms]![frmAdvQry]![cboKeyword]),[tblNotebook_Sub]![Title],[forms]![frmAdvQry]!"*"+[cboKeyword]+"*&quot
but it doesn't take.
2:I would also like to setup something where the users could input a start and end date using the same form. I made two text boxes (txtStart and txtEnd) but I have no idea where in a IIF(IsNull) statement to put the Between argument.

I appreciate the help, thanks.

 

[scriverb]

Try the following:

1. IIf(IsNull([forms]![frmAdvQry]![cboKeyword]),[tblNotebook_Sub]![Title], Like "*" & [forms]![frmAdvQry]![cboKeyword] & "*&quot
This would select all titles if there was no entry selected in cboKeyword but if a selection was made(i.e. "Bob&quot then the criteia would be Like "*Bob*"

2. In the criteria line put the following with updates as to form names:
Between [Forms]![frmYourFormName]![txtStart] and [Forms]![frmYourFormName]![txtEnd]

This should do it for you. Bob Scriver

 

[randaj]

The first one doesn't seem to be working. If I leave the field empty everything is returned, but as soon as I enter in a value nothing is returned. I tried putting () around the Like argument, switching the & for +. Nothing is working.

Also the second one I would like to be able to use that IsNull argument if possible.

Thanks scriverb, your pushing this rookie right along.

 

[scriverb]

I have made adjustments to both items:

1. Needed Single quotes around criteria:
IIf(IsNull([forms]![frmAdvQry]![cboKeyword]),[tblNotebook_Sub]![Title], Like "'*" & [forms]![frmAdvQry]![cboKeyword] & "*'&quot

2. I have used the Switch function here and given you four entry options-1)Entry of both dates(Returns Date Range), 2)Entry of Start date only(returns dates >= startdate), 3)Entry of End Date only(returns dates <= enddate, 4)Both dates are left blank(returns all records)
Switch(Not IsNull([Forms]![frmYourFormName]![txtStart]) and Not IsNull([Forms]![frmYourFormName]![txtEnd]) , Between [Forms]![frmYourFormName]![txtStart] and [Forms]![frmYourFormName]![txtEnd], Not IsNull([Forms]![frmYourFormName]![txtStart]) and IsNull([Forms]![frmYourFormName]![txtEnd]), >= [Forms]![frmYourFormName]![txtStart]), IsNull([Forms]![frmYourFormName]![txtStart]) and Not IsNull([Forms]![frmYourFormName]![txtEnd]), <= IsNull([Forms]![frmYourFormName]![txtEnd]), IsNull([Forms]![frmYourFormName]![txtStart]) and IsNull([Forms]![frmYourFormName]![txtEnd]), [TableName]![DateFieldName])

NOTE: You will have to update the item in red of #2 as I don't know the name of your table or the date field name.

This is a little more than you asked for but it gives you a full range of selection options.
Bob Scriver

 

[randaj]

Sorry for the delay, but the first one is still not working. Same problem as above.

 

[scriverb]

Here is a new WHERE statement for your query. Just copy and paste over the entire old WHERE statement on the SQL screen of the query.
WHERE (((tblNotebook_Sub.Title)=IIf(IsNull([Forms]![frmAdvQry]![cboKeyword]),[tblNotebook_Sub]![Title],&quot;&quot)) OR (((InStr(1,[tblNotebook_Sub]![Title],[Forms]![frmAdvQry]![cboKeyword]))>0));

I changed the way of looking for your keyword from the Like idea to looking for the string in the Title using the Instr function.
Bob Scriver

 

[randaj]

Good ole Invalid Syntax pops up.

 

[scriverb]

Don't see anything that stands out as wrong with the code. I have made a couple of sq. bracket modifications but nothing major. Cut and paste the blue code below into your SQL overwriting the WHERE portion of the SQL. When you get done pasting copy all of the SQL and post it here so that I may review it for you.
WHERE ((([tblNotebook_Sub]![Title])=IIf(IsNull([Forms]![frmAdvQry]![cboKeyword]),[tblNotebook_Sub]![Title],&quot;&quot)) OR (((InStr(1,[tblNotebook_Sub]![Title],[Forms]![frmAdvQry]![cboKeyword]))>0));

Here is the code that works with the tables I am testing on. This works perfectly. Use the above where statement with the modifications and look over the SQL below as this works. Try to be more descriptive of what the syntax error is referring to please.

SELECT Diagnosis.Name
FROM Diagnosis
WHERE (((Diagnosis.Name)=IIf(IsNull([Forms]![frmSelectName]![txtSelectCriteria]),[Diagnosis]![Name],&quot;&quot)) OR (((InStr(1,[Diagnosis]![Name],[Forms]![frmSelectName]![txtSelectCriteria]))>0));
Bob Scriver

 

[randaj]

This is what I currently have:

SELECT tblNotebook_Main.Notebook, tblNotebook_Main.Researcher, tblNotebook_Sub.Project, tblNotebook_Sub.Title, tblNotebook_Sub.Date
FROM tblNotebook_Main INNER JOIN tblNotebook_Sub ON tblNotebook_Main.Notebook = tblNotebook_Sub.Notebook
GROUP BY tblNotebook_Main.Notebook, tblNotebook_Main.Researcher, tblNotebook_Sub.Project, tblNotebook_Sub.Title, tblNotebook_Sub.Date
HAVING (((tblNotebook_Main.Notebook)=IIf(IsNull([forms]![frmAdvQry]![cboNotebook]),[tblNotebook_Main]![Notebook],[forms]![frmAdvQry]![cboNotebook])) AND ((tblNotebook_Main.Researcher)=IIf(IsNull([forms]![frmAdvQry]![cboResearcher]),[tblNotebook_Main]![Researcher],[forms]![frmAdvQry]![cboResearcher])) AND ((tblNotebook_Sub.Project)=IIf(IsNull([forms]![frmAdvQry]![cboProject]),[tblNotebook_Sub]![Project],[forms]![frmAdvQry]![cboProject])) AND ((tblNotebook_Sub.Title)=IIf(IsNull([forms]![frmAdvQry]![txtKeyword]),[tblNotebook_Sub]![Title],([tblNotebook_Sub].[Title]) Like &quot;'*&quot; & [forms]![frmAdvQry]![txtKeyword] & &quot;*'&quot) AND ((tblNotebook_Sub.Date) Between [Start Date] And [End Date]))
ORDER BY tblNotebook_Main.Notebook, tblNotebook_Sub.Project;

If you don't mind, could you show me where to put the new statement.

 

[scriverb]

Try this. The red code is where I put the new code. Since you are using the HAVING clause we will try it there.
SELECT tblNotebook_Main.Notebook, tblNotebook_Main.Researcher, tblNotebook_Sub.Project, tblNotebook_Sub.Title, tblNotebook_Sub.Date
FROM tblNotebook_Main INNER JOIN tblNotebook_Sub ON tblNotebook_Main.Notebook = tblNotebook_Sub.Notebook
GROUP BY tblNotebook_Main.Notebook, tblNotebook_Main.Researcher, tblNotebook_Sub.Project, tblNotebook_Sub.Title, tblNotebook_Sub.Date
HAVING (((tblNotebook_Main.Notebook)=IIf(IsNull([forms]![frmAdvQry]![cboNotebook]),[tblNotebook_Main]![Notebook],[forms]![frmAdvQry]![cboNotebook])) AND ((tblNotebook_Main.Researcher)=IIf(IsNull([forms]![frmAdvQry]![cboResearcher]),[tblNotebook_Main]![Researcher],[forms]![frmAdvQry]![cboResearcher])) AND ((tblNotebook_Sub.Project)=IIf(IsNull([forms]![frmAdvQry]![cboProject]),[tblNotebook_Sub]![Project],[forms]![frmAdvQry]![cboProject])) AND ((([tblNotebook_Sub]![Title])=IIf(IsNull([Forms]![frmAdvQry]![cboKeyword]),[tblNotebook_Sub]![Title],&quot;&quot)) OR (((InStr(1,[tblNotebook_Sub]![Title],[Forms]![frmAdvQry]![cboKeyword]))>0)) AND ((tblNotebook_Sub.Date) Between [Start Date] And [End Date]))
ORDER BY tblNotebook_Main.Notebook, tblNotebook_Sub.Project;

Let me know how that works out.
Bob Scriver

 

[randaj]

You are my hero. Thanks for all the tips, I really appreciate it.

 

[scriverb]

Hooray!! Did that work for you. If it didn't we were going to add a seperate WHERE statement after the FROM clause. Good luck with this project. Sorry it took so long to get it just right. It was a little tricky. Bob Scriver