Search critieria from Form

G

Guest

I recieved some ideas from my first posting but I can not get this to work.
I have a query set up that will return results based on what is entered in a
form. Is there a way I can have the query ignore a field if nothing is
entered in it? My current code is as follows but it will return everything
that matches the criteria as well as every record that has a blank for that
field. I only want the record to return if it matches that field not if it
is blank.
(([JFWorkflow Main Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*" Or
([JFWorkflow Main Table].CN_PN) Is Null
I wanted to use an IIF but was told that I can not use a Like inside an IIF.
Somethink like this:
IIF(([JFWorkflow Main Table].CN_PN) Is Not Null,([JFWorkflow Main
Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*",)
Any suggestions how to accomplish this?
Thanks again.
 
S

Steve Schapel

Consjoe,

I am a little unclear about what you are really wanting. If it is this...
"I only want the record to return if it matches that field not if it is
blank", then the Where clause is simple like this...
([JFWorkflow Main Table].CN_PN = [Forms]![Search]![CN_PN])
 
G

Guest

Right, I understand that but I am giong for a little deeper.
First I want them to only have to enter the beggining of the search
criteria, that is why I am using Like ...&"*"
Second, if they enter "212" for CN_PN and nothing for (a different field)
name I don't want the results to only show CN_PN of 212... where the name is
blank. I want it to show all 212... records regardless of what is in name,
blank or not. I have 20 fields they can use to narrow down their search
which is why I wanted to try the IIF. I only want the query to pull
information from the Search form if that field is not Null, if it is Null
then the query should not limit the results by this field.
I hope I have made it a little easier to understand, sorry for the confusion.


Steve Schapel said:
Consjoe,

I am a little unclear about what you are really wanting. If it is this...
"I only want the record to return if it matches that field not if it is
blank", then the Where clause is simple like this...
([JFWorkflow Main Table].CN_PN = [Forms]![Search]![CN_PN])

--
Steve Schapel, Microsoft Access MVP
I recieved some ideas from my first posting but I can not get this to work.
I have a query set up that will return results based on what is entered in a
form. Is there a way I can have the query ignore a field if nothing is
entered in it? My current code is as follows but it will return everything
that matches the criteria as well as every record that has a blank for that
field. I only want the record to return if it matches that field not if it
is blank.
(([JFWorkflow Main Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*" Or
([JFWorkflow Main Table].CN_PN) Is Null
I wanted to use an IIF but was told that I can not use a Like inside an IIF.
Somethink like this:
IIF(([JFWorkflow Main Table].CN_PN) Is Not Null,([JFWorkflow Main
Table].CN_PN) Like [Forms]![Search]![CN_PN] & "*",)
Any suggestions how to accomplish this?
Thanks again.
Click to expand...
Click to expand...
 
S

Steve Schapel

Consjoe,

Ok, thanks for the further explanation. We're getting closer!

How about if they enter "212" for CN_PN, and they also enter "XYZ" for
(another field)? Do you want the query to return all records with "212"
in CN_PN and also all the records with "XYZ" in (the other field)? Or
do you want it to return only those records with "212" in CN_PN and
"XYZ" in (the other field)?
 
G

Guest

I only want the records with "212" in CN_PN and "XLZ" in (the other field).
I only want records that match ALL criteria entered.
I am having so many problems with the query that I am not looking into
Filter by Form (but I can't use Like "*" with the filter) and I am also
looking at an If statement On Open of the Search Results Form.
But, if you can help me get the query working I am still up for that. Just
thought I would tell you some of my other ideas.
Thanks again.
 
S

Steve Schapel

Consjoe,

Ok, I am pretty sure I understand what you are getting at. If so, this
is how the Where clause of the query...
WHERE (CN_PN Like [Forms]![Search]![CN_PN] & "*" Or
[Forms]![Search]![CN_PN] Is Null) And (Other Like
[Forms]![Search]![Other] & "*" Or [Forms]![Search]![Other] Is Null)

Try that, and let us know.
 
G

Guest

That will return all CN_PN that match the criteria AS WELL AS all CN_PN that
is Null, correct?
I only want CN_PN to return if it matches the critera; not if it matches the
criteria OR is Null
Sorry I am being so difficult.
Thanks again

Steve Schapel said:
Consjoe,

Ok, I am pretty sure I understand what you are getting at. If so, this
is how the Where clause of the query...
WHERE (CN_PN Like [Forms]![Search]![CN_PN] & "*" Or
[Forms]![Search]![CN_PN] Is Null) And (Other Like
[Forms]![Search]![Other] & "*" Or [Forms]![Search]![Other] Is Null)

Try that, and let us know.

--
Steve Schapel, Microsoft Access MVP
I only want the records with "212" in CN_PN and "XLZ" in (the other field).
I only want records that match ALL criteria entered.
I am having so many problems with the query that I am not looking into
Filter by Form (but I can't use Like "*" with the filter) and I am also
looking at an If statement On Open of the Search Results Form.
But, if you can help me get the query working I am still up for that. Just
thought I would tell you some of my other ideas.
Thanks again.
Click to expand...
Click to expand...
 
W

wazza_c12

Hi Guys, I think I am having the same problems as mentioned here and
just posted a question about, I have been using a query similar to the
one just suggested but getting strange results, it's driving me mad.
Please let me know if you figure out how to make it work.
Regards
Wazza
 
S

Steve Schapel

Consjoe,
That will return all CN_PN that match the criteria AS WELL AS all CN_PN that
is Null, correct?
Click to expand...

No, this is not correct.
I only want CN_PN to return if it matches the critera; not if it matches the
criteria OR is Null
Click to expand...

Well, did you try it? I am 99.9% sure it will do what you are asking.
Sorry I am being so difficult.
Click to expand...

Well, no problem.
Thanks again
Click to expand...

You're welcome!
 
G

Guest

THANK YOU!!!!!
It works!!!
I thought it was going to return Null values also because I was thinking of
Like [Forms]![Search]![CN_PN] & "*" Or ([JFWorkflow Main Table].CN_PN) Is
Null
as apposed to
Like [Forms]![Search]![CN_PN] & "*" Or [Forms]![Search]![CN_PN] Is Null
I have been working on this for so long, you have no idea how happy I am
that this is now solved. Thank You Very Much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query with info from a form 4
If Statement with "*" in Query 2
IIF statement in criteria not working 1
Parameter Query Form Help 2
#error - Blank 3
Requerying Automatically 1
iif function syntax problem 5
Blank fields 3

Top