blank fields

[Guest]

I have a query for a table.

some fields in the table are blank.

however the query results only those records where all field have some value.

How can I display records in which some fields have value and some dont?

 

[Wayne Morgan]

Have you placed criteria on the fields that would limit them from being
shown? Please open your query in SQL view then copy and paste the SQL view
into your message. Also, please explain further what you're wanting to see.

 

[Allen Browne]

You need to change the criteria in your query to allow Nulls, or possibly
use an outer join if the query contains more than one table.

Both issues are explained in this article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Guest]

The sql view look like
SELECT uniquew.[Supporter No], uniquew.[Post Code], uniquew.[Last Name],
uniquew.[First Name]
FROM uniquew
WHERE (((uniquew.[Supporter No]) Like [Forms]![main]![Text20] & "*") AND
((uniquew.[Post Code]) Like [Forms]![main]![Text15] & "*") AND
((uniquew.[Last Name]) Like "*" & [Forms]![main]![Text31] & "*") AND
((uniquew.[First Name]) Like "*" & [Forms]![main]![Text45] & "*"));

What I want is that if any of the field is blank (or empty) the record
should still be displayed on the query.
so for example. post code is blank , it should still display the record if
it matches the criteria.

 

[Wayne Morgan]

See if this does what you want:

SELECT uniquew.[Supporter No], uniquew.[Post Code], uniquew.[Last Name],
uniquew.[First Name]
FROM uniquew
WHERE ((((uniquew.[Supporter No]) Like [Forms]![main]![Text20] & "*") OR
uniquew.[Supporter No] Is Null) AND
(((uniquew.[Post Code]) Like [Forms]![main]![Text15] & "*") OR
uniquew.[Post Code] Is Null) AND
(((uniquew.[Last Name]) Like "*" & [Forms]![main]![Text31] & "*") OR
uniquew.[Last Name] Is Null) AND
(((uniquew.[First Name]) Like "*" & [Forms]![main]![Text45] & "*")) OR
uniquew.[First Name] Is Null);

 

[Guest]

I hope this message still reaches you after so much time

The problem now is it also displays all the blank fields

Is there way to say.. first display the fields that has some info...

See if this does what you want:

SELECT uniquew.[Supporter No], uniquew.[Post Code], uniquew.[Last Name],
uniquew.[First Name]
FROM uniquew
WHERE ((((uniquew.[Supporter No]) Like [Forms]![main]![Text20] & "*") OR
uniquew.[Supporter No] Is Null) AND
(((uniquew.[Post Code]) Like [Forms]![main]![Text15] & "*") OR
uniquew.[Post Code] Is Null) AND
(((uniquew.[Last Name]) Like "*" & [Forms]![main]![Text31] & "*") OR
uniquew.[Last Name] Is Null) AND
(((uniquew.[First Name]) Like "*" & [Forms]![main]![Text45] & "*")) OR
uniquew.[First Name] Is Null);

--
Wayne Morgan
MS Access MVP

The sql view look like
SELECT uniquew.[Supporter No], uniquew.[Post Code], uniquew.[Last Name],
uniquew.[First Name]
FROM uniquew
WHERE (((uniquew.[Supporter No]) Like [Forms]![main]![Text20] & "*") AND
((uniquew.[Post Code]) Like [Forms]![main]![Text15] & "*") AND
((uniquew.[Last Name]) Like "*" & [Forms]![main]![Text31] & "*") AND
((uniquew.[First Name]) Like "*" & [Forms]![main]![Text45] & "*"));

What I want is that if any of the field is blank (or empty) the record
should still be displayed on the query.
so for example. post code is blank , it should still display the record
if
it matches the criteria.