Parameter Query

  • Thread starter Thread starter Scott Matheny
  • Start date Start date
S

Scott Matheny

I'm using the parameter
Like [Forms]![TestSearch]![a field] & "*" , but that only
popultes records that have values in their fields. How
can I make the query find Null values if the parameter I
enter is just ""?
 
not sure but sending a query to find nothing may be the
problem. i am assuming that's what the query is returning.
you may have to add a second parameter where field 1 is
not null and field 2 is null.
 
Dear Scott:

WHERE (SomeColumn Like [Forms]![TestSearch]![a field] & "*"
OR ([Forms]![TestSearch]![a field] IS NULL AND SomeColumn = ""))

You would edit this in the SQL view.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

I tried running this in SQL view (which I know little
about) and in the criteria column, but both return an
unknown WHERE. Please give any advice.

Thanks,
-Scott
 
Dear Scott:

If you're putting this into the query design grid, you don't use the
WHERE. Giving step by step instructions on how to create things in
the grid would take a LOT of time. In this newsgroup we commonly
refer to the query in SQL View, which is text and lends itself to
messages we can quickly and easily send. You might gain from looking
at your queries using SQL View.

The SQL syntax does not allow WHERE more than once in a simple query.
I didn't know for sure if you already had some filtering (that's what
WHERE does) or not. You may need to construct your query in the SQL
View by adding what I suggested with AND in place of WHERE.

I would be much more informed about what you are doing, and could post
a complete query in my response, if you would open the SQL View and
post what you have there already into this newsgroup. That would save
a lot of trouble for you.

After you have the SQL working, you can look at the Design Grid and
see how you could have performed what you want there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

This is my SQL statement:

SELECT Too many field to paste
FROM TestSponsor LEFT JOIN CompressionData ON TestSponsor.
[Test Key] = CompressionData.[Remote Key]
WHERE (((TestSponsor.[Sponsor Company Key]) Like [Forms]!
[TestSearch]![SponsorCompany] And (TestSponsor.[Sponsor
Company Key]) Like "*")) OR (((TestSponsor.[Sponsor
Company Key])="") AND (([Forms]![TestSearch]!
[SponsorCompany]) Is Null));

It doesn't like my WHERE very much though, as I get the
message that my expression is invalid. In the design
grid, it just shows my original criteria with "" in the
or. I don't see why it wouldn't like that, but I'm sure
I'm missing the logic here.

Thanks in advance,
-Scott
 
Dear Scott:

Simplified a bit, this is what you have:

WHERE (TestSponsor.[Sponsor Company Key] Like
[Forms]![TestSearch]![SponsorCompany] And TestSponsor.[Sponsor
Company Key] Like "*")
OR (TestSponsor.[Sponsor Company Key] = "" AND
[Forms]![TestSearch]![SponsorCompany] Is Null);

Looking at this one part at a time:

TestSponsor.[Sponsor Company Key] Like
[Forms]![TestSearch]![SponsorCompany]

There may or may not be wildcards embedded within the SponsorCompany,
but if not, then this is basically the same as asking if the two are
equal.

TestSponsor.[Sponsor Company Key] Like "*"

Asking if the value is like anything is almost meaningless.

TestSponsor.[Sponsor Company Key] = ""

Without LIKE, the equals means it must literally be an asterisk.
Probably not what you intended.

[Forms]![TestSearch]![SponsorCompany] Is Null

This sounds OK.

Now, what is it you want to accomplish?

I'm thinking right now you are having trouble working through the
logic, as well as designing criteria that make sense. I need to know
which things confuse you so I can spotlight them in my reply.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
TestSponsor.[Sponsor Company Key] Like
[Forms]![TestSearch]![SponsorCompany]
Click to expand...
This is a dropdown list, so there are no wildcards. I
assume this is correct.
TestSponsor.[Sponsor Company Key] Like "*"
Click to expand...
I was told that Like "*" would return any value, including
nulls, but I'm fairly confused by this statement.
Now, what is it you want to accomplish?
Click to expand...

What I want is to write a parameter query where the
parameters come from values in a form. I have the code
written to accomplish all of these things, but my logic
for the parameters is off.

The logic I really want is that if the person chooses to
enter a value into a field, it returns records with that
value, and if they don't input one, it will return any
value for that field.

That said, I don't want putting nothing in to be
represented as looking for Null values, I want nothing to
mean that there is no criteria, the query can return any
value.

I hope that I made clear what I'm aiming for (and that
what I'm aiming for isn't too far off the deep end.

Thanks for being so patient,
-Scott
 
Dear Scott:

Based on what you posted before, I think maybe you want something like
this:

WHERE (TestSponsor.[Sponsor Company Key] =
[Forms]![TestSearch]![SponsorCompany]
OR [Forms]![TestSearch]![SponsorCompany] IS NULL)

This says that the Sponsor Company Key must be exactly what was
entered in the SponsorCompany control, or the SponsorCompany control
is empty.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,
TestSponsor.[Sponsor Company Key] Like
[Forms]![TestSearch]![SponsorCompany]
Click to expand...
This is a dropdown list, so there are no wildcards. I
assume this is correct.
TestSponsor.[Sponsor Company Key] Like "*"
Click to expand...
I was told that Like "*" would return any value, including
nulls, but I'm fairly confused by this statement.
Now, what is it you want to accomplish?
Click to expand...

What I want is to write a parameter query where the
parameters come from values in a form. I have the code
written to accomplish all of these things, but my logic
for the parameters is off.

The logic I really want is that if the person chooses to
enter a value into a field, it returns records with that
value, and if they don't input one, it will return any
value for that field.

That said, I don't want putting nothing in to be
represented as looking for Null values, I want nothing to
mean that there is no criteria, the query can return any
value.

I hope that I made clear what I'm aiming for (and that
what I'm aiming for isn't too far off the deep end.

Thanks for being so patient,
-Scott
Click to expand...
 
Tom,
WHERE (TestSponsor.[Sponsor Company Key] =
[Forms]![TestSearch]![SponsorCompany]
OR [Forms]![TestSearch]![SponsorCompany] IS NULL)
Click to expand...

This yields an expression that is too complex to be
evaluated. Also, the logic of the statement is still
really fuzzy.

Maybe what I'm trying to do isn't logical, but seems to be
the best way to coordinate a search. If there's a
different logic I should be using, then please tell me
what that is and an example of how to use it.

Thanks so much,
-Scott
 
Dear Scott:

The logic I recommend is exampled in the query fragment I wrote. It
is that either the [Sponsor Company Key] must be the value in the
[SponsorCompany] control on your form or else the control is empty.

To approach the "too complex" problem, please post the new SQL you
have built with my suggesting and I'll look at that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,
WHERE (TestSponsor.[Sponsor Company Key] =
[Forms]![TestSearch]![SponsorCompany]
OR [Forms]![TestSearch]![SponsorCompany] IS NULL)
Click to expand...

This yields an expression that is too complex to be
evaluated. Also, the logic of the statement is still
really fuzzy.

Maybe what I'm trying to do isn't logical, but seems to be
the best way to coordinate a search. If there's a
different logic I should be using, then please tell me
what that is and an example of how to use it.

Thanks so much,
-Scott
Click to expand...
 
I have it working for the most part.

Thank you so much for your patience and help.

::Kisses feet::

-Scott
 
I used the statement on two seperate fields, and the query
works perfectly when there are values for both or Nulls
for both, but when one has a value and the other is Null,
the query doesn't disply any records.

This leads me to believe that I would have to write more
SQL to correspond between the two, but I DON'T want to
write SQL to correspond 15 fields.

If this a simple problem, great, but if not, then I
believe my cause is lost.

Thanks,
-Scott
 
Back
Top