Query based on Form Fields

C

consjoe

I have a form where a user can search by Company and Plan Code, they can
enter criteria in both or leave one blank. The problem I am having is that
if a user searches only by Plan Code, they should see all items matching that
Plan Code no matter what the Company is, even the items with a blank Company.
However, if they enter criteria in the Plan Code and Company fields they
should not see items with a blank Company in the results, only items matching
that company.
But the query I am useing to get my results is not working correctly. I am
using this is my Company Field
IIf([Forms]![frmSearch]![Company Code] Is Null,"*",Like
[Forms]![frmSearch]![Company Code] & "*")
I think there is a problem with the true segment because if I leave Company
blank on the form I get zero results.
I have also tried
IIf([Forms]![frmSearch]![Company Code] Is Null,[Company Code],Like
[Forms]![frmSearch]![Company Code] & "*")
If Company is left blank I get all records that don't have a Null Company
however, I do not get records with a Null Company.

Basically what I want is if you enter criteria you will only get results
matching that criteria, however, if you leave a criteria field blank you will
receive all records with something in that fields as well as all records that
are null.
Thanks in advance.
 
K

Klatuu

I did something similar. I don't know how this will work with the Null's,
but it did work for me, but I don't remember if I had Nulls in the field
Like Nz([Forms]![frmSearch]![Company Code],"*")))
 
C

consjoe

Thank You but still haveing problems.
I have not used the Nz function very often but I was unable to get it to
work in this situation. It could be something I am doing wrong but I am not
sure.

Klatuu said:
I did something similar. I don't know how this will work with the Null's,
but it did work for me, but I don't remember if I had Nulls in the field
Like Nz([Forms]![frmSearch]![Company Code],"*")))

consjoe said:
I have a form where a user can search by Company and Plan Code, they can
enter criteria in both or leave one blank. The problem I am having is that
if a user searches only by Plan Code, they should see all items matching that
Plan Code no matter what the Company is, even the items with a blank Company.
However, if they enter criteria in the Plan Code and Company fields they
should not see items with a blank Company in the results, only items matching
that company.
But the query I am useing to get my results is not working correctly. I am
using this is my Company Field
IIf([Forms]![frmSearch]![Company Code] Is Null,"*",Like
[Forms]![frmSearch]![Company Code] & "*")
I think there is a problem with the true segment because if I leave Company
blank on the form I get zero results.
I have also tried
IIf([Forms]![frmSearch]![Company Code] Is Null,[Company Code],Like
[Forms]![frmSearch]![Company Code] & "*")
If Company is left blank I get all records that don't have a Null Company
however, I do not get records with a Null Company.

Basically what I want is if you enter criteria you will only get results
matching that criteria, however, if you leave a criteria field blank you will
receive all records with something in that fields as well as all records that
are null.
Thanks in advance.
Click to expand...
Click to expand...
 
C

consjoe

I have used this method in the past but it is not working for me in this
instnace. I think it is because some items do not have a company code at
all. It looks like that is causing the below method to fail. Any experience
with this?

Gary Walter said:
a common method is like:

WHERE
([Company]
LIKE [Forms]![frmSearch]![Company Code] & "*"
OR
[Forms]![frmSearch]![Company Code] IS NULL)
AND
([Plan Code]
LIKE [Forms]![frmSearch]![Plan Code] & "*"
OR
[Forms]![frmSearch]![Plan Code] IS NULL)


consjoe said:
Thank You but still haveing problems.
I have not used the Nz function very often but I was unable to get it to
work in this situation. It could be something I am doing wrong but I am
not
sure.

Klatuu said:
I did something similar. I don't know how this will work with the
Null's,
but it did work for me, but I don't remember if I had Nulls in the field
Like Nz([Forms]![frmSearch]![Company Code],"*")))

:

I have a form where a user can search by Company and Plan Code, they
can
enter criteria in both or leave one blank. The problem I am having is
that
if a user searches only by Plan Code, they should see all items
matching that
Plan Code no matter what the Company is, even the items with a blank
Company.
However, if they enter criteria in the Plan Code and Company fields
they
should not see items with a blank Company in the results, only items
matching
that company.
But the query I am useing to get my results is not working correctly.
I am
using this is my Company Field
IIf([Forms]![frmSearch]![Company Code] Is Null,"*",Like
[Forms]![frmSearch]![Company Code] & "*")
I think there is a problem with the true segment because if I leave
Company
blank on the form I get zero results.
I have also tried
IIf([Forms]![frmSearch]![Company Code] Is Null,[Company Code],Like
[Forms]![frmSearch]![Company Code] & "*")
If Company is left blank I get all records that don't have a Null
Company
however, I do not get records with a Null Company.

Basically what I want is if you enter criteria you will only get
results
matching that criteria, however, if you leave a criteria field blank
you will
receive all records with something in that fields as well as all
records that
are null.
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Top