Multiple Selection Criterial Query some with NULL fields

J

Joe M.

I have a form containing several text boxes which supplies selection
criterial for several fields in a query. Usually data is not entered in all
the text boxes. I use a default astrick (*) to make sure I get results from
the query for the fields left blank. However 1 field in the query sometimes
is blank for some records. When the astrick is entered in this field, only
non-blank records are reuurned. I need ALL records for this field returned
whether it is blank or not. Can someone help with this?

Many thanks,
Joe M.
 
J

Joe M.

Steve,

When I run the query with astrick for all parameters it still returns only
the records without the null field.

Thanks,
Joe M.
 
J

Joe M.

Steve,
I need to add the null statement even for the columns that don't contain any
nulls? Could you please explain this?

Many thanks,
Joe M.
 
J

Joe M.

Steve,

I filled my query per your instructions. If I leave all parameters blank
then everything is returned which is correct. But if I fill any values then
nothing is returned. So there's a problem. I appreciate your help. Any
further clarification or instructions are greatly appreciated.

Thanks,
Joe M.
 
J

Joe M.

Here's the SQL:

SELECT ZZ_UM_CAOR.Persno, ZZ_UM_CAOR.[Employee/appname], ZZ_UM_CAOR.Date,
ZZ_UM_CAOR.[Rec order], ZZ_UM_CAOR.Hours, ZZ_UM_CAOR.MLCPremCd,
ZZ_UM_CAOR.[Send CCtr], ZZ_UM_CAOR.ActTyp, ZZ_UM_CAOR.[Rec CCtr],
ZZ_UM_CAOR.[A/AType], ZZ_UM_CAOR.[Last chnge], ZZ_UM_CAOR.Change
FROM ZZ_UM_CAOR
WHERE (((ZZ_UM_CAOR.Persno) Like [forms]![ZZ_UM_CAOR Form].[Person_Number])
AND ((ZZ_UM_CAOR.[Employee/appname]) Like [forms]![ZZ_UM_CAOR
Form].[Person_Name]) AND ((ZZ_UM_CAOR.[Rec order]) Like [forms]![ZZ_UM_CAOR
Form].[MWO_number]) AND ((ZZ_UM_CAOR.[A/AType])="LikeLike [forms]![ZZ_UM_CAOR
Form].[MWO_number]")) OR ((([forms]![ZZ_UM_CAOR Form].[Person_Number]) Is
Null) AND (([forms]![ZZ_UM_CAOR Form].[Person_Name]) Is Null) AND
(([forms]![ZZ_UM_CAOR Form].[MWO_number]) Is Null) AND
((([ZZ_UM_CAOR].[A/AType]) Like [forms]![ZZ_UM_CAOR Form].[MWO_number]) Is
Null));
 
J

Joe M.

Steve,

I think I made all the corrections. Unfortunately, when i enter in any
paramater I get no results. Only when I leave everything blank is everything
returned. below is the SQL. Thanks for your continued assistance.

Joe M.

SELECT ZZ_UM_CAOR.Persno, ZZ_UM_CAOR.[Employee/appname], ZZ_UM_CAOR.Date,
ZZ_UM_CAOR.[Rec order], ZZ_UM_CAOR.Hours, ZZ_UM_CAOR.MLCPremCd,
ZZ_UM_CAOR.[Send CCtr], ZZ_UM_CAOR.ActTyp, ZZ_UM_CAOR.[Rec CCtr],
ZZ_UM_CAOR.[A/AType], ZZ_UM_CAOR.[Last chnge], ZZ_UM_CAOR.Change
FROM ZZ_UM_CAOR
WHERE (((ZZ_UM_CAOR.Persno)=[forms]![ZZ_UM_CAOR Form].[Person_Number]) AND
((ZZ_UM_CAOR.[Employee/appname])=[forms]![ZZ_UM_CAOR Form].[Person_Name]) AND
((ZZ_UM_CAOR.[Rec order])=[forms]![ZZ_UM_CAOR Form].[MWO_number]) AND
((ZZ_UM_CAOR.[A/AType])=[forms]![ZZ_UM_CAOR Form].[AA_Type])) OR
((([forms]![ZZ_UM_CAOR Form].[Person_Number]) Is Null) AND
(([forms]![ZZ_UM_CAOR Form].[Person_Name]) Is Null) AND (([forms]![ZZ_UM_CAOR
Form].[MWO_number]) Is Null) AND (([forms]![ZZ_UM_CAOR Form].[AA_Type]) Is
Null));


Steve said:
Joe,

You need to go through each criteria and check that it is actually what you
want and that the syntax is correct.

1. Remove all "like" operators

2. Note the two Likes in this line:
((ZZ_UM_CAOR.[A/AType])="LikeLike [forms]![ZZ_UM_CAOR
Form].[MWO_number]"))
Click to expand...

3. There should not be double quoyes in this line

4. Something is wrong here:
AND
((([ZZ_UM_CAOR].[A/AType]) Like [forms]![ZZ_UM_CAOR Form].[MWO_number]) Is
Null));
Click to expand...

Steve



Joe M. said:
Here's the SQL:

SELECT ZZ_UM_CAOR.Persno, ZZ_UM_CAOR.[Employee/appname], ZZ_UM_CAOR.Date,
ZZ_UM_CAOR.[Rec order], ZZ_UM_CAOR.Hours, ZZ_UM_CAOR.MLCPremCd,
ZZ_UM_CAOR.[Send CCtr], ZZ_UM_CAOR.ActTyp, ZZ_UM_CAOR.[Rec CCtr],
ZZ_UM_CAOR.[A/AType], ZZ_UM_CAOR.[Last chnge], ZZ_UM_CAOR.Change
FROM ZZ_UM_CAOR
WHERE (((ZZ_UM_CAOR.Persno) Like [forms]![ZZ_UM_CAOR
Form].[Person_Number])
AND ((ZZ_UM_CAOR.[Employee/appname]) Like [forms]![ZZ_UM_CAOR
Form].[Person_Name]) AND ((ZZ_UM_CAOR.[Rec order]) Like
[forms]![ZZ_UM_CAOR
Form].[MWO_number]) AND ((ZZ_UM_CAOR.[A/AType])="LikeLike
[forms]![ZZ_UM_CAOR
Form].[MWO_number]")) OR ((([forms]![ZZ_UM_CAOR Form].[Person_Number]) Is
Null) AND (([forms]![ZZ_UM_CAOR Form].[Person_Name]) Is Null) AND
(([forms]![ZZ_UM_CAOR Form].[MWO_number]) Is Null) AND
((([ZZ_UM_CAOR].[A/AType]) Like [forms]![ZZ_UM_CAOR Form].[MWO_number]) Is
Null));



Steve said:
OK! Now open your query in SQL view, copy all that is there and paste it
into a post here so I can take a look at it.

Steve


Steve,

I filled my query per your instructions. If I leave all parameters
blank
then everything is returned which is correct. But if I fill any values
then
nothing is returned. So there's a problem. I appreciate your help. Any
further clarification or instructions are greatly appreciated.

Thanks,
Joe M.

:

Joe,

Whether certain columns in your records have Null values is
irrelevant.
Null
comes into play where you could leave one or more of your textboxes
blank.
When you leave a textbox blank, it has a Null value and you must
account
for
this in your query expression; hense the second part of the query
expreesion
I gave you.

Try putting the expression I gave you for each column your criteria
depends
on a textbox. Put some value in each textbox and run your query. Does
your
query return all the records you expect?

Make one of the textboxes blank and run your query again. Does your
query
return all the records you expect?

Steve
(e-mail address removed)


Steve,
I need to add the null statement even for the columns that don't
contain
any
nulls? Could you please explain this?

Many thanks,
Joe M.

:

Joe,

You need to use the expression I gave you for each textbox you use
for
criteria in your query. Say your form is named FrmCustomerCriteria
and
you
have three textboxes named TxtCriteria1, TxtCriteria2 and
TxtCriteria3
for
separate criteria. In the appropriate fields in your query you need
to
use
the following expressions for criteria:
Forms!FrmCustomerCriteria!TxtCriteria1 Or
(Forms!FrmCustomerCriteria!TxtCriteria1 Is Null)
Forms!FrmCustomerCriteria!TxtCriteria2 Or
(Forms!FrmCustomerCriteria!TxtCriteria2 Is Null)
Forms!FrmCustomerCriteria!TxtCriteria3 Or
(Forms!FrmCustomerCriteria!TxtCriteria3 Is Null)

You can now leave one or more of the textboxes blank and your query
will
return the appropriate records for the non-blank criteria you set.

Steve



Steve,

When I run the query with astrick for all parameters it still
returns
only
the records without the null field.

Thanks,
Joe M.

:

Use this for the criteria.............

Forms!MyForm!MyTextbox Or (Forms!MyForm!MyTextbox Is Null)

Steve
(e-mail address removed)



I have a form containing several text boxes which supplies
selection
criterial for several fields in a query. Usually data is not
entered
in
all
the text boxes. I use a default astrick (*) to make sure I get
results
from
the query for the fields left blank. However 1 field in the
query
sometimes
is blank for some records. When the astrick is entered in this
field,
only
non-blank records are reuurned. I need ALL records for this
field
returned
whether it is blank or not. Can someone help with this?

Many thanks,
Joe M.
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 with 2 field selection criteria 1
Filter By Selection error for blank fields 2
Help with "Like" syntax in query criteria 5
Displaying query result on the same form as selection fields 6
Query 1
If Null Show All Records 2
How to enter a Wildcard or Is Null into a query paramater ? 7
field Null but not Null 9

Top