Query based on Form Fields

G

Guest

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

Guest

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],"*")))
 
G

Guest

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

Gary Walter

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],"*")))

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...
Click to expand...
 
G

Guest

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

Gary Walter

maybe I did not fully understand
your logic?

some simple data might help...

Company Code Plan Code
A 1
2
B 1
C 2
1
D 3

if
[frmSearch]![Company Code] is blank
and
[frmSearch]![Plan Code] = 1,
you want to return?

Company Code Plan Code
A 1
B 1
1

if that is so, then method should have
worked unless

1) logic bracketing got screwed up
when Access saved query....
check SQL View and verify
(in fact, copy and paste here)

2) [frmSearch]![Company Code] has a space
so looks Null but isn't

WHERE
(
[Company]
LIKE [Forms]![frmSearch]![Company Code] & "*"
OR
Len(Trim([Forms]![frmSearch]![Company Code]))=0
)
AND
(
[Plan Code]
LIKE [Forms]![frmSearch]![Plan Code] & "*"
OR
Len(Trim([Forms]![frmSearch]![Plan Code]))=0
)





consjoe said:
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.

:

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

Gary Walter

WHERE
(
[Company]
LIKE [Forms]![frmSearch]![Company Code] & "*"
OR
Len(Trim([Forms]![frmSearch]![Company Code] & ""))=0
)
AND
(
[Plan Code]
LIKE [Forms]![frmSearch]![Plan Code] & "*"
OR
Len(Trim([Forms]![frmSearch]![Plan Code] & ""))=0
)


Gary Walter said:
maybe I did not fully understand
your logic?

some simple data might help...

Company Code Plan Code
A 1
2
B 1
C 2
1
D 3

if
[frmSearch]![Company Code] is blank
and
[frmSearch]![Plan Code] = 1,
you want to return?

Company Code Plan Code
A 1
B 1
1

if that is so, then method should have
worked unless

1) logic bracketing got screwed up
when Access saved query....
check SQL View and verify
(in fact, copy and paste here)

2) [frmSearch]![Company Code] has a space
so looks Null but isn't

WHERE
(
[Company]
LIKE [Forms]![frmSearch]![Company Code] & "*"
OR
Len(Trim([Forms]![frmSearch]![Company Code]))=0
)
AND
(
[Plan Code]
LIKE [Forms]![frmSearch]![Plan Code] & "*"
OR
Len(Trim([Forms]![frmSearch]![Plan Code]))=0
)





consjoe said:
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)


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

:

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

Gary Walter

one other thought (I wish I understood
what "fail" means specifically...)

you are only looking at "right side"
with wildcard....

is it possible you meant?

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

:

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

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 relelated to 2 combo boxes 2
Access Determine what query to run based on a checkbox 0
yes/no or both Error 3071 2
Erratic results from query criteria 3
NEED HELP with a multi-criteria query 2
IIF Statement In Query Doesn't Work Right 3
problems with parametized query 1
Parameter Query based on Form 2

Top