Q. I need to base a query on 2 different fields.

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

Jim Jones

Hi,

I need to create a query that will display only the records where one
of the fields (which is text) in the set is blank, and another field
which is numeric is 0.

The query is has two tables. One is the primary key, but NO fields are
selected from it. The only fields selected are from the many side
table.

The actual query in use is used by a form.
But that form, due to a quirk with the "next record" button, creates
extra, uneeded records, and insists on saying "filtered" next to it.

No one seems to have an answer for that problem.

So, I want to create a duplicate query, with the criteria above to
delete those extra records, and either set it to a button, or code,
upon opening the pop-up form.

WHAT I NEED FROM THIS NG:
What I'd like to know from here, is why my query isn't working, when
put =" " In the "queryfield1" and on "row" where I put the >0
cireteria, under "queryfield2".

It will still show some of the 0 records with Blank "queryfield1"
fields.

I need it to display all the records that have blank queryfield1
fields AND all records which have 0 in the queryfield2 fields.

I need this to be done in one query.

Thanks,
Jim
 
Hi Jim,

You may have a couple of problems with the query. For
the blank field, you are specifying criteria of " ".
This will work if a space has been entered into the
field, but not if the field is blank (they look the same,
but they are different to Access). To retrieve blank
fields you need to enter Is Null as the criteria. To
retreive both blank records and records with a space you
could enter Is Null OR " ".

The other problem that you may be encountering is that
you may be putting criteria for both fields on the same
criteria row. This implies an AND condition, which will
only return records that meet both conditions. It sounds
like you want an OR condition, so you would have to put
the criteria for each field on a separate line.

HTH

-Ted Allen
 
Dear Jim:

When you have a problem, simplify.

I suggest you temporarily remove the queryfield2 entirely and see if
the filtering for queryfield1 is working correctly. I believe you
will see that it is not.

Your filter appears to be limiting the results to those rows in which
queryfield1 is a single space. It is not unlikely that this column
contains values which you think of as being blank, but which are not
composed of a single space.

There are two prominent possibilities.

The value may be null in many cases. This is quite different from
being a single space, or an empty string. In fact, it may well be
that the value is either null or empty in all cases.

Using this as a starting point, I suggest you first convert nulls into
empty strings for purposes of this filter:

Nz(queryfield1, "")

Make this a calculated column in the query and filter it with "", not
" ".

If you do have some rows in which this column is one or more spaces,
you should use a trim function:

RTrim(Nz(queryfield1, ""))

Put this in as the calculated column. Continue comparing it to "" in
the filter criterion.

When this is working well, replace the test for queryfield2 and
continue with testing that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Jim:

When you have a problem, simplify.

I suggest you temporarily remove the queryfield2 entirely and see if
the filtering for queryfield1 is working correctly. I believe you
will see that it is not.

Your filter appears to be limiting the results to those rows in which
queryfield1 is a single space. It is not unlikely that this column
contains values which you think of as being blank, but which are not
composed of a single space.

There are two prominent possibilities.

The value may be null in many cases. This is quite different from
being a single space, or an empty string. In fact, it may well be
that the value is either null or empty in all cases.

Using this as a starting point, I suggest you first convert nulls into
empty strings for purposes of this filter:

Nz(queryfield1, "")

Make this a calculated column in the query and filter it with "", not
" ".

If you do have some rows in which this column is one or more spaces,
you should use a trim function:

RTrim(Nz(queryfield1, ""))

Put this in as the calculated column. Continue comparing it to "" in
the filter criterion.

When this is working well, replace the test for queryfield2 and
continue with testing that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...


Ted,

For the sole purpose of simplicity, I used your example, and can't
tell you enough how grateful I am that this solved my problem

Tom,

I have printed your response too, as it is clearly more technical, and
what I want to do is become more proficient like you and Ted, in
developing my projects. I'm going to study your example and likely
implement it, when I learn well enough exactly what it means and
does..

The problem is, I can't find printed material that is written well
enough for me to develop my little projects, so I come to the ngs to
post my problems.

To this date, I've not found a better solution than posting my Access
questions n the various groups.

If you can suggest helpful sites and books that don't weigh 20 pounds,
I'd be grateful. I already know about Rogersaccesslibrary.com, which
has helped in a couple of other things.

Thanks,
Jim
 
Jim,

If it were me, I would redo the original query attached to your form button. Access has over 400 bugs (2002 version), and I have chased my tail for hours trying to understand why perfectly good code would not work properly. It has been my experience that the best solution is to delete the code and re-write the query. It may be that simple.

Wayne
Click to expand...

Wayne,

Thanks, but in my case, it was lack of knowledge.

Jim
 
Hi Jim,

Glad you got it to work. Unfortunately I don't really
have any good resources to pass on. I've mostly picked
things up from the Access and VB help over the years, and
from reading posts in this forum. You may be able to
find some recommendations from searching previous posts
though. It seems like it comes up fairly often.

-Ted Allen
 
Wayne said:
Jim,

If it were me, I would redo the original query attached to your form
Click to expand...
button. Access has over 400 bugs (2002 version), and I have chased my tail
for hours trying to understand why perfectly good code would not work
properly. It has been my experience that the best solution is to delete the
code

I have had similar experiences with Access, so you are not alone.

and re-write the query. It may be that simple.
 
Back
Top