Hello,
I am having many problems with setting up a parameter query that
searches by the criteria entered or returns all records if nothing is
entered.
I have designed an unbound form with 3 fields on it: Date (DateSpan1
and DateSpan2), Originator, and GroupName. I have added a button that
triggers a query and uses those fields as its parameter criteria to
populate a form. The user must be allowed to either enter all of the
criteria or only one if they choose.
I have researched the issue on other posts and have seen several
examples of how other people have tackled the issue, but I have been
unable to get any of them to work. I was wondering if some kind soul
could take a look at my syntax and point out to me what I am doing
wrong.
The form the user enters the search criteria on is named "Search
Entry"
The query is named simply "Search"
On "Search Entry" the unbound controls are named DateSpan1, DateSpan2,
Originator, and GroupName
Failed Approach 1:
Several posts stated that if you entered in the criteria in query
design view referencing the control on your form, and then set the OR
statement to Is Null that you would return either the criteria the
user entered or everything if they entered nothing. Made sense to me.
I typed in the following in the query design grid.
Field: Originator
Criteria: [forms]![Search Entry]![Originator]
Or: [forms]![Search Entry]![Originator] Is Null
This statement returned the results exactly as I wanted only when I
entered this on a single column. If I tried to enter similar criteria
and "Is Nulls"on multiple columns (originator + date + GroupName)
leaving one of the criteria blank returned an empty recordset. I
couldn't figure out the Is Nulls were being ignored when multiple
parameters were specified so I tried a different approach.
Failed Approach 2:
Field: Originator
Criteria: IIf(IsNull([forms]![Search Entry]![Originator]),"*",
[forms]![Search Entry]![Originator])
Criteria: IIf(IsNull([forms]![Search Entry]![Originator]),
[forms]![Search Entry]![Originator] = "*", [forms]![Search
Entry]![Originator])
Criteria: IIf(IsNull([forms]![Search Entry]![Originator]),like "*",
[forms]![Search Entry]![Originator])
I pulled the first example of this off another post, and modified it
several times to try and get it to work. These successfully returned
results when criteria was entered, but returned an empty recordset
when left blank.
Failed Approach 3:
Field: Originator
Criteria: IIf(nz([forms]![Search Entry]![Originator],"")="",like
"*",[forms]![Search Entry]![Originator])
Another approach I found on another post. Again this returned a result
when criteria was entered but a blank recordset when nothing was
entered. I didn't have any luck changing this one around because I
didn't fully understand the syntax here. Seemed to me (with my limited
experience) there that there was one too many arguments, but any
fudging around with it popped up syntax errors.
Approach 2 seemed the easiest to me and I can't for the life of me
figure out why it will not return any records when a wildcard is
entered. When I enter a simple "*" in the criteria it works, but not
when I have it in coded like in the above examples.
I'm on a harsh deadline to get this done. ANY help anybody could give
me would be greatly appreciated!
Thanks in advance.
P.S. I am using Access 2002 if that means anything