Don't list empty fields

[James Ivey]

In a query, is there a way to say
"If this field is empty, then don't list it"

James

 

[James Ivey]

Wait, I don't think I asked that question very well. What I meant to say
is:

In a query, is there a way to say
"If this field is empty, then don't list the record"

James

 

[pietlinden]

Wait, I don't think I asked that question very well. What I meant to say
is:

In a query, is there a way to say
"If this field is empty, then don't list the record"

James

SELECT ...
FROM...
WHERE MyTable.[Some Field] IS NOT NULL;

 

[James Ivey]

Thank you (e-mail address removed)

But I should have mentioned that I am a very green Newbie at Access, and am
not a programmer.

Could you tell me a little more about how to do that?

Thank you.
James

Wait, I don't think I asked that question very well. What I meant to say
is:

In a query, is there a way to say
"If this field is empty, then don't list the record"

James

SELECT ...
FROM...
WHERE MyTable.[Some Field] IS NOT NULL;

 

[pietlinden]

Thank you (e-mail address removed)

But I should have mentioned that I am a very green Newbie at Access, and am
not a programmer.

Could you tell me a little more about how to do that?

Thank you.
James

SELECT ...
FROM...
WHERE MyTable.[Some Field] IS NOT NULL;

You're kidding, right?
Same way you create any other select query in Access.
Click on the Queries tab,
create a new one.
drop the two tables into your query.
join the two tables on the Primary|Foreign keys.
right click on the line between the two tables.
Select either 2 or 3... show all records from ... and choose the table
that containes all the records.
Full outer join? (allow both tables to have missing records) - that's
harder. Can't be done directly in Access SQL without a union.

 

[John W. Vinson]

Could you tell me a little more about how to do that?

Create a query based on your table.
Select all the fields that you want to see.
In the Criteria line underneath the field in question type the exact text

IS NOT NULL

The query will then show only records for which there is data in that field.

Then - to see what Piet was getting at - select View... SQL on the menu. The
query grid is just a tool, a way to construct queries; the *real* query is the
SQL that it constructs. If you're communicating about queries on the
newsgroups, post the SQL - it may look cryptic now, but to those of us who've
been using databases for a while it's like reading plain text.

John W. Vinson [MVP]

 

[James Ivey]

Sweet! IS NOT NULL is exactly what I am after.

Thank a bunch John - I really appreciate the explanation.

And yes, I found the SQL. Thanks for the tip on posting the SQL... will do.

And thank you too, Piet. Like I said, I'm a very green Newbie. I haven't
gotten much further than just building a neat-looking form <g>

Thanks again you guys.
James