Search form

[NISMOJim]

I've been given a database to overhaul. Currently it uses the switchboard to run reports as follows;
Report by date range
Report by name
Report by permit number


Report by company
Report by class

I thought it would be pretty fancy to have just one form used to search by whatever criteria the user fills in using the following text/combo boxes;
Enter start date
Enter end date
Enter name ... etc.

This way the user could enter just a name on the form to see a report of all the classes (along with the other information) that someone has attended. Or they could enter a class to see all of the people who have taken the class. They could also narrow it down to see if a particular person had attended a particular class between a certain date range. If there is nothing in the textbox, the query would include all records.

So far I've built the form and entered the following criteria all on the And line of the query for each field;
Permit - [Forms]![frmSearchPermit]![UBPermit]
Issue Date - Between [Forms]![frmSearchPermit]![UBStart] And [Forms]! [frmSearchPermit]![UBEnd]
Name - [Forms]![frmSearchPermit]![UBName]
Class - [Forms]![frmSearchPermit]![UBClass]

I was getting an error message saying the expression is typed incorrectly or is too complex to be evaluated, but now I don't get any results at all. I'm sure the reason is probably because it is looking for null values in the fields where nothing is entered in the search boxes. How can I overcome this obstacle to get the form working to get results whether one box is entered or any combination of boxes are filled in?

Thanks for any help offered!

[orange]

You might want to read thru this tutorial.
http://www.fontstuff.com/access/acctut19.htm

[alansidman]

Here is a video tutorial on search forms

http://www.datapigtechnologies.com/f...earchform.html

Alan

[NISMOJim]

Both are great sources of information, and most everything is working well now except for the date references. I added Like(Between [Forms]![Search]![Start] And [Forms]![Search]![End])&"*" to the conditions line in the query. Now no records show up at all when I search by name, company, date range or any combination. What is the proper format for finding the Issue date using Start and End date ranges, or entering nothing at all in these boxes?
Thanks for this valuable information.

[alansidman]

Try removing the Like and & "*" features from the criteria. Will you always have a start and end date in your search?



Alan

[NISMOJim]

Not always, but I would like to give the user that option if at all possible. Sometimes all the information we're given is that "a few people went through a particular training sometime around November". The date range could narrow things down quite a bit.

[June7]

Think maybe the BETWEEN AND operators can only work with dates, not Null or empty string, and have to handle the empty box and provide an alternate value. I never set up a query with dynamic criteria so this is not tested but try:

Assuming the box has Null if nothing entered:
Issue Date - Between Nz([Forms]![frmSearchPermit]![UBStart], #1/1/1900#) And Nz([Forms]![frmSearchPermit]![UBEnd], Date())

Or to play it safe and handle either Null or empty string:
Issue Date - Between Iif([Forms]![frmSearchPermit]![UBStart] & "" = "", #1/1/1900#, [Forms]![frmSearchPermit]![UBStart]) And Iif([Forms]![frmSearchPermit]![UBEnd] & "" = "", Date(), [Forms]![frmSearchPermit]![UBEnd])

Otherwise, might be able use the >= <= operators instead of BETWEEN AND.

[NISMOJim]

This gives me an error message;
This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

[June7]

Okay, I just tested my suggestion using the Nz function and it worked. I had only the date field as criteria and no table joins. I suggest you do a test with a query that has only the date criteria and see what happens. Post your query SQL statement for analysis if still have issue.

[NISMOJim]

I tried the second one with the IF statements on the same criteria line as all the other fields with search criteria. Not sure I understand it, but it works! Thanks so much for the help. I will be referring to this expression many times in the future.
Thanks again!