Blank Query Parameter Problems

[Andy]

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

[Keith Wilby]

ak***********@hotmail.com (Andy) wrote:

Field: Originator
Criteria: [forms]![Search Entry]![Originator]
Or: [forms]![Search Entry]![Originator] Is Null

Hi Andy,

Try this in your criteria:

IIf(Not IsNull([forms]![Search Entry]![Originator]),[forms]![Search
Entry]![Originator],*,)

This leaves you with just one line of criteria for each field in your query
and should make your "or" conditions work.

Regards,
Keith.
www.keithwilby.org.uk

[Keith Wilby]

ak***********@hotmail.com (Andy) wrote:

Field: Originator
Criteria: [forms]![Search Entry]![Originator]
Or: [forms]![Search Entry]![Originator] Is Null

Hi Andy,

The only way I could make this work was to call functions from the query
criteria. In my example I have 2 fields, ID (Long) and FF (Date) on the
query grid in that order. The unbound form is called "frmEB" and the
unbound text boxes "txtFF" and "txtID".

Query criteria is:

Criteria: Like libID()
Or: Like libFF()

Code is:

Public Function libFF() As Variant

If (IsNull([Forms]![frmEB]![txtFF])) Then
libFF = "" 'Return zero-length string if the text box is empty
Exit Function
Else
libFF = CDate([Forms]![frmEB]![txtFF])
End If

Debug.Print libFF

End Function

Public Function libID() As Variant

If (IsNull([Forms]![frmEB]![txtID])) And IsNull([Forms]![frmEB]![txtFF])
Then
libID = "*" 'Return wildcard if both text boxes empty
Exit Function
Else
libID = [Forms]![frmEB]![txtID]
End If

Debug.Print libID

End Function
HTH - Keith.
www.keithwilby.org.uk

[Andy]

Kieth,

First I'd like to thank you for your reply; I appreciate the help. On
to my question regarding your 2nd example of code here. I am not that
adept at VB or VBA for that matter. Although I have an idea of the
general concept you have outlined here I have had some trouble
implementing it. I'm not too familiar with the Function routine. I
tried simplifying your code to see if I could get a query to call a
value defined in a function but didn't have any luck. I just don't
remember how do to it from the VB class I took 2 years ago =(

First I tried creating a module with this function in it, but I was
unable to get the query to reference the function in the module. I
have vague memories of needing to define an instance or something like
that in a module before you can use it, but nothing specific.

I then tried calling the function directly from the cmdClick on the
search page. This did go to the function and the variable in the
function was set to the value I wanted, but it set a "Function not
defined" error when it tried to open up the results display form.

I was wondering if anybody could a.) Show me what I'm missing with the
function command or b.) Show me how to reference a public variable in
the criteria line of a query.

Thanks in advance

[Pieter Linden]

Why reinvent the wheel? Try this:
http://www.mvps.org/access/forms/frm0045.htm

[Andy]

never mind, I figured it out. I had just misspelled something *sigh*.
Only took me 3 hours to figure that out.

Thanks anyways =)

[Keith Wilby]

pi********@hotmail.com (Pieter Linden) wrote:

Why reinvent the wheel? Try this:
http://www.mvps.org/access/forms/frm0045.htm

Thanks for that Pieter, an excellent utility :o)