473,890 Members | 2,051 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Blank Query Parameter Problems

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
Nov 13 '05 #1
6 7142
ak***********@h otmail.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
Nov 13 '05 #2
ak***********@h otmail.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
Nov 13 '05 #3
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
Nov 13 '05 #4
Why reinvent the wheel? Try this:
http://www.mvps.org/access/forms/frm0045.htm
Nov 13 '05 #5
never mind, I figured it out. I had just misspelled something *sigh*.
Only took me 3 hours to figure that out.

Thanks anyways =)
Nov 13 '05 #6
pi********@hotm ail.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)
Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5849
by: The Plankmeister | last post by:
Hi... I have a query which I'm accessing through PHP as a stored procedure. However, I need to be able not to pass a couple of parameters in certain situations. When I do this, I get an error: Parameter has no default value. But it works fine in Access when I run the query from there and just hit
15
7208
by: deko | last post by:
I need a way to create a table with a programmatically defined name. I have a Make Table query that will create the table with the name that I put in the query, but I don't know how to (or if I can) use a function to pass the table name into the query. Here is the basic form of the Mke Tble query: SELECT TxDate, Amount, Tx_ID, TxAcctName, INTO FROM tblTxJournal;
3
1011
by: cassandra.flowers | last post by:
Hi, I was wondering if it is possible (Using access) to have a query parameter as a drop down box rather than a text box? e.g. typing as criteria for a query produces a box with a text box for you to enter the parameter. Can any body enlighten me on how to turn this text box into a drop down box? And whether it is possible?
4
8235
by: Lightning Tony | last post by:
This is probably a really silly question but I am exhausted and cant seem to find the answer. I have a query that I run from a command button on a form. The query has three criteria set. The first is built into the form and so is not a problem. Then the second and third criteria prompt me for parameters. This all works fine and I get the info I want broken down the way I want to see it. But sometimes I dont want to filter at all on these...
3
4111
by: Uwe | last post by:
Howdy! I've googled and googled for this without success, and either it's so easy that nobody has ever asked, or there is some serious reason nobody would do this. But I'm going to ask anyway. I'd like to move my ASP.NET session ID from the default cookie into the URL. I tried the "cookieless" approach and it doesn't work for me for two reasons:
0
2191
by: Billie Boy | last post by:
Hi to all. I’m new here and am coming to you from Melbourne Australia. So a big HELLO 2 ALL. Now I am encountering an annoying problem in the SQL builder of the copy of VB.6 that I am using at home. My problem is that it’s driving me NUTS NUTS NUTS. I am connected to a data connection within my computer that accesses tables & views from a MS Access file.
0
1512
by: HomerS007 | last post by:
Hi, I'm using asp.net 2.0 and sql server 2000 for my first ever project. On one of the page in the application, I want to limit what the user can see based on his/her login. It's a page that has sensitive personal info like social security number and I only want user to see their own information and no one else. Can anybody please tell me how to pass in the username as a query parameter? I used a DetailsView control to and configure...
5
5026
by: power2005 | last post by:
Hi Experts I'm having a problem with IIf statement and it's driving me crazy... i'm a total newbie and i've spent hours to make it work but to no avail... what I'm trying to do is to have a search form with several parameters, and i want to ignore the parameter when it's blank, i tried the following criteria IIf(IsNull(!!),"*",!!) and IIf(IsNull(!!), Like "*",!!) however, when the parameter is empty nothing comes out, only when i...
1
4896
by: codexxx | last post by:
Hi All, I have got a requirement from a client who wants the query parameter to be hidden in the browser. What he wants is like following. If a url link becomes like http://mysite.com/profile.php?id=34 then it should be replaced by something like http://mysite.com/profile.php/34 or some thing like this. So please help me by giving some suggestions.
0
9975
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9812
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11212
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10799
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9614
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8004
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6032
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4655
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4255
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.