What's going on??!!

[cstuckey]

I'm trying to run a simple query to take out the the null values in my table. I'm putting "Is not null" in the criteria but when I run the query it brings back all the records. What's going on? I'm linking two tables. I don't see anything wrong with the properties.

Please help.

Thanks,
Chris

 

[Mark Wild]

I expect it might be a problem wit the type of link that you have.

In design view, right click on hte join and amend it from 1 to 2 or 3 as relevant.

If you are unsure, set up a new query and use the unmatched query wizard. THis will step you through and should clarify things.

HTH

 

[dcx693]

I'm trying to run a simple query to take out the the null values in my table. I'm putting "Is not null" in the criteria but when I run the query it brings back all the records.

First, if you've removed all the Nulls in your table, then Is Not Null would certainly give you all the records.

If, however, you are still trying to remove the Nulls, then why are you using "Is Not Null" in your criteria?

Can you explain a bit more?

 

[cstuckey]

I'm trying to make a table that only shows where our sales reps are spliting commissions with another rep. On table is all of the sales activity for the month, the other is the salesman number and name and other information. I'm linking the field "SLS" to one table to "REP" to the other table. Both SLS and REP represent the Salesman's number. Is this more helpful?

 

[Pat Hartman]

Sounds like the field contains a zero length string. It looks the same as a null field but it is not null. Add criteria to your query.

Where YourField Is Not Null AND YourField <> "";

 

[cstuckey]

I figured it out. When I use >0 it works. I'm not understanding why it works with this symbol since the field is a "text" field

Chris

 

[Pat Hartman]

Because the ASCII value for 0 is higher than the ASCII value for null and and the ASCII value for space but lower than the ASCII value for letters.

However since you don't understand why the statement works, it is likely that others who look at the criteria won't undersand it either. Therefore, it is better to use the compound condition that I suggested which makes your intent clear. Relying on "tricks" is poor practice.