Search critieria from Form

C

consjoe

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])
 
C

consjoe

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)?
 
C

consjoe

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.
 
C

consjoe

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!
 
Top