Searching for value range on a custom search form

[DB88]

Hello,

I built a custom search form that uses unbound text boxes to set the criteria for a query. For example, I have an unbound text box, LastName on my search form. Then in the query, I set the criteria to Like "*"&[Forms]![SearchForm]![LastName]&"*". This returns all records if the text box is left blank or returns those records that match the text box if it isn't.

I would like to do the same thing but instead of searching a text field, I want to search a number field. And I want to search for a range of values aka >10 instead of just a single value. I still need the query to return all records if the text box is left blank.

Thanks

[ranman256]

I would set up an if

select case true
case isnull(lastname] and isnull([txtNum])
openquery qsListAll

case isnumeric([txtNum]) then
openquery qsListByNum

case else
openquery qsListByName
end select

[DB88]

Thanks for your quick reply. Could you please explain the code? I've only been using Access for a few weeks and have gained about an intermediate level knowledge.

[June7]

Try:

>Nz([textbox], 0)

Will these fields always have data? If Null is allowed then need to handle the records with Null or they won't be retrieved. One way:

"*" & [Forms]![SearchForm]![LastName] & "*" Or Is Null

Another is to calculate a field in query and apply criteria to that field:

FieldNameAdj: Nz([FieldName], "none")

[DB88]

The search for LastName is done and works great. Now, I'm focusing on NumberField.

In reference to your question, "Will these fields always have data?":
If you mean the unbound text box, then no. I'm providing a few different search options and the user won't always use all of them.
If you mean the actual field in the record I'm search then no. Not every record has complete information. But I don't want to grab the null because it doesn't satisfy the criteria.

>Nz([textbox],0) doesn't quite work because it doesn't return all records if textbox is blank. Also, it only does ">" search. Sorry, I wan't clear before. I want the user to type ">10" or they could type "<10". I want them to be able to search for the range they desire. So the ">" or "<" will be user specified.

I think your idea about calculating a field in the query and then apply criteria is the way to go.

[June7]

Then dynamic parameterized query might not be suitable approach. Maybe need VBA to set Filter property of form or to use in DoCmd.OpenForm (or OpenReport) method. Review http://www.allenbrowne.com/ser-62code.html

[DB88]

Yep, it looks like I'm going to have to bite the bullet and learn VB to build this search form. I have another thread going regarding other desired features of this form and the feedback is saying VB.

I was hoping Access would be user friendly enough for me to give my users basic functionality quickly. But looks like even basic stuff requires VB.

Thanks for the link! It is going to be extremly helpful

[June7]

The more user friendly, the more code. I have one very simple non-split db that hasn't a single line of code, not even macros - nada. Users rely on one complex form/subform(s) for data entry/edit and intrinsic search/filter tools. They can build queries and reports as they wish. The 'they' is a small staff of research professionals and that's how they wanted it.

What you are attempting is well beyond 'basic' functionality.

[DB88]

Yeah, it just seems basic to me because my other program's built-in features are so much more user friendly. It took minimal development to do what I'm attempting here. I need to stop approaching problems the same way I would in my other program.

I also have a small group of users like you. Maybe it would just be faster to teach them enough Access for them to build their own queries. But they really don't understand databases. They were calling 10 seperate single tab excel spreadsheets with copy/paste paragraph information that was neither searchable or filterable their database before I got here.