excludea record in a report based on criteria...

[markjkubicki]

this is a bit of a band-aid approach;
never-the-less, it "seems" to be the most efficient / immediate attack...
it's also one of those SQL tongue twisters that hang me up...




I have a report with (2) tables in the record source (tbeNotes, tbeFTypeDetails)
In it, I want to include all of the records in tbeNotes
- EXCEPT the record where its [optionNumber] = 62,
IF...
. there is any record in tbeFTypeDetalis where
. [SubReqmntsYN] (boolean) is true
. AND
. [SubReqmntsDetails] (long text) has an entry (len() =0)

I'm got this expression in my recordsource:

Expr1: Not ([tbeFTypeDetails]![SubReqmntsYN] And Len(Nz([tbeFTypeDetails]![SubReqmntsDetails],""))=0 And [tbeNotes]![optionNumber]=62)

in the results, tbeNotes.[optionNumber] = 62 is correctly not printing when it ought not to, and is printnig when it ought to;
BUT...
I'm also getting multiple listings of all of the other records in tbeNotes, apparently, one time for for every record in tbeFTypeDetails

any help will be greatly appreciated in advance,
mark

[Micron]

The report is based on a query and that query contains both tables which have one or more common fields and there is at least one join?
I see this in query design view as
- notes on the left, details on the right with an outer join (arrow pointing to details)
- criteria on optionNumber as <> 62; YN field as = True
- SubReqmntsDetails: this one I don't get. How can the length of string data in the field be 0 yet there is data? " has an entry ". If all you want to do is ensure there is data in that field, Is Not Null should work. Otherwise, you are not doing yourself any favors by putting criteria on a memo (long text) type of field. I don't advocate putting this type of field in the same table of some other entity. If for example, we're talking about work order task notes, these notes would go into their own table, linked by work order/task id as foreign key(s). A query would automatically eliminate records with no notes by having an equal join between the fk of notes table and the pk of some other table. Eliminates "holes" in your main table as well (where there are no notes).

[markjkubicki]

May be the root of the problem
There are no common fields between the two tables

I simply added the second table into the record source (at the form, when you click the “...” so that the criteria could be evaluated

Ok... the more I think about it.... geez, that’s an obvious one

Still need to get to the end goal if you have a suggestion

ALSO, good point about the “not isnull()”
Thnx much

[Micron]

You're dealing with a query, so it is Is Not Null or Is Null. IsNull(someThing) is vba syntax; Is Null is sql.
I have never heard of adding a second table to the source for any form or report if that's what you're saying. I would have expected it to replace one source for another, not add one. While some would disagree with me, I almost never would base a report or form on a table. One reason is that it's so much easier to modify a query in terms of filtering, sorting or what have you, not to mention that so often multiple tables are involved in a data source so what other choice is there? Regardless, IMHO you will never solve this without a query. If there is no relationship between the 2 tables, that indicates a design problem when it comes to normalization. Your table names and reported issue suggests that there ought to be, unless these tables are not of your making.

In the end, you may have to start with a Cartesian query (where no fields are joined) which will produce an exponential number of records that you'd then have to figure out how to link together. My preferred approach would be to have related fields between tables, and memo/comment fields in their own table.