Avatar of Fritz Paul
Fritz Paul
Flag for South Africa asked on

Access query does not display records with empty fields - Can you help please

I have a query that gets its criteria values from a form.
I have cleared the form so there are no filters/constraints. That means Combo405 and Combo406 "below" are empty (set equal to "").
However when any field in the table is empty, that record does not show up in the query.
For instance i have two records with the same [Property] value, but one does not have a value in the [Owner] field. Then my query only displays the record that has a property value and a Owner value in the underlying table.

My query looks like below.


View of query
Microsoft Access

Avatar of undefined
Last Comment
Fritz Paul

8/22/2022 - Mon
Mike Eghtebas

use NZ(MyField) for those fields

TheOwner: Nz( [Owner])     '<-- this inserts empty string for the missing owners.

or

TheOwner: Nz( [Owner],"tbd")
Fritz Paul

ASKER
Hi,

My database design view now looks like below. The query now includes even those records without values. Thanks.

But now the query is not updatable anymore, because I changed all the names adding an X after the name. If I keep the query field names the same I get a circular reference error.

Is there a way that I can still keep the query updatable?

Please what do you mean by the "tbd"?

New query design view.
ASKER CERTIFIED SOLUTION
Mike Eghtebas

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Fritz Paul

ASKER
Thanks a lot.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck