Multi-field search form/query criteria

[emorris1000]

I'm putting together a big multi-field search form and was wondering about how to do the criteria.

At first I just did stuff like <[forms]![fSearchPoly]![tbI2Max], but then I realized that if they left the field I2Max empty it wouldn't return any results. My first thought is to build an iff function into the criteria, but that seems unnecessarily complex, is there a simpler way to have the criteria pass null values as true?

 

[pbaldy]

For a couple of fields I would do this:

http://access.mvps.org/access/queries/qry0001.htm

But for many, I tend to create SQL on the fly, as in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

 

[emorris1000]

Yeah I was considering programmatically building the SQL string. There's going to be like 30 possible criteria, and I know how to do it fairly well. I'm just not 100% on how to use that as a rowsource for a listbox. Wish I could look at the sampledb, not allowed to download stuff like that at work.

Good rundown in your link though. That first one may be the key, just not sure how to perform both greater than and less than (2 controls for it.)

So far I have

SELECT TGeneralPoly.NotebookNum, TGeneralPoly.[I-2]
FROM TGeneralPoly
WHERE ((TGeneralPoly.[I-2])<[forms]![fSearchPoly]![tbI2Max]) OR (([forms]![fSearchPoly]![tbI2Max] Is Null));

, I just can't figure out how to do it for both > & <.........


Well wiat, it's not hard to do both, I just can't have it only do one or the other (like they leave the "min" field blank. I could just require that they include both min and max if they are using this field....

 

[pbaldy]

You'd build the SQL and then

Forms!FormName.ListboxName.RowSource = YourSQLVariableHere

 

[emorris1000]

well, I figured out how to write the SQL in a non VBA-generated way, not sure how fast this will run, but so far it's pretty good. Basically you just have to switch the AND and OR operators a bit:

.....
WHERE ((TGeneralPoly.[Density]>[Forms]![fSearchPoly]![tbDensityMin] OR [Forms]![fSearchPoly]![tbDensityMin] Is Null) AND (TGeneralPoly.[Density]<[Forms]![fSearchPoly]![tbDensityMax] OR [Forms]![fSearchPoly]![tbDensityMax] is Null))
......

This will give me all values for [Density] between the min and max text boxes (tbDensityMin, tbDensityMax), and ignore the null boxes.

You just can't put this query back into design mode or it will create some ridiculously overly complex SQL. I'm going to see how well this works, if it runs quickly I'll just leave it like this (need to make a notepad copy of it though just to make sure that I can recover if Access tries to make it's own Death By a Thousand Parenthesis version of it.)


Edit: Oh wow lol, with 4 of these (for 4 sets of min/max textboxes), if I switch to "design mode" Access builds something with like 200 Criteria. gg Access. You just got dunked on.

Edit2: ohhh wow this is tricky. Is there a way to go from a rowsource property directly to the SQL view? When I go through the property screen of the listbox this query is in to get to the query, it creates the 200 Criteria version, and then tries to generate the SQL from it, which is like 20 pages long and completely dumpsters the program.....

Maybe I should just assign the rowsource programattically.

 

[pbaldy]

That's why I only use that technique if there are just a couple of criteria.

If the listbox rowsource is that query, you shouldn't have to assign it in code, just requery the listbox.

 

[emorris1000]

An additional question. When someone enters a value into a min-max search box they can delete the value and the lbox will update correctly recognizing these values as not being used. But I also have 2 combo-boxes, these don't seem to have a "null" option, so unless I programattically reset the values to null (with a button), I don't know if the user has a way to switch the combo-boxes back to null.

I thought of jamming in a "<None Selected>" option and just switch the Is Not Null option to "= "<None Selected>", but is there an easier way to do this? Not that this way isn't easy, it's just kludgy as it involves me putting a "<None Selected>" value into the table that the combo box draws from, which feels wrong to me.

ed: nm apparently there is a null value already in there, I'm just a bit of a dufus and wrote the query wrong. It's working now as intended. Awesome.

 

[pbaldy]

You can use this, so the combo would still be Null:

http://access.mvps.org/access/forms/frm0043.htm