Why is my query pulling one record only?

[pablofonto]

Hello Everyone,

I've been trying to figure out why my query only pulls record Object ID 10011399 and not the other one, but I can't figure it out. Can anyone please help me see what's wrong?

Thanks,

Pablo

 

[pbaldy]

I suspect you've got a mix of ZLS and Null in the subty field, thus they fail the join. If I change them all to 1, I get 2 records.

 

[RuralGuy]

Put both [EndDate] fields next to each other and remove the qualifier and see which of the 4 records qualify.

 

[pablofonto]

I see what PBALDY is saying, is probably something with that field being NULL.
Now I don't understand what you RuralGuy are telling me. This thing is driving me crazy, because I thought about getting rid of the Subty join, but I needed for other things that I have in the table. I just didn't include it in my test file. Please let me know if you think of other ideas to get rid of this problem.

thanks,

Pablo

 

[pbaldy]

It could be argued that a field that contains Nulls is not a good candidate for a join field. Since you say it has to be joined for other reasons, I'd make sure it can't be Null. The Null is certainly the cause of the problem posted here.

 

[RuralGuy]

I guess I'm just all wet but the criteria in your query is
WHERE [ActivityTable].[End date] < [ActivityTable_1].[End date]
and there is *only* one record that meets that criteria.

 

[pablofonto]

RuralGuy the dates for the two records were the same, so the criteria should met both records. I appreciate you guys trying to help me with this issue.
Here I uploaded another table where some records with the NULL fields are also pulled in the query, you will also see why I have the join on the subty as well. I can't understand why this is happening. The table named ActivityTable New is the one you guys saw earlier.

 

[pablofonto]

RuralGuy the dates for the two records were the same, so the criteria should met both records. I appreciate you guys trying to help me with this issue.
Here I uploaded another table where some records with the NULL fields are also pulled in the query, you will also see why I have the join on the subty as well. I can't understand why this is happening. The table named ActivityTable New is the one you guys saw earlier.

I forgot the file, here it is!

 

[pbaldy]

None of those has a Null in that field. Easy enough to see with:

SELECT ActivityTable.ObjectID, nz([Subty],"null here!") AS Expr1
FROM ActivityTable;

or

SELECT ActivityTable.ObjectID, ActivityTable.Subty
FROM ActivityTable
WHERE (((ActivityTable.Subty) Is Null));

 

[pablofonto]

Paul, I don't get it. How can when I open the table there lots of Subty that are blank? if they're not null, then what's their value?
Sorry, if I'm making a stupid question, but I'm not an advance user.

thanks for your time on this!

Pablo

 

[pbaldy]

They contain a zero length string (ZLS). There's a decent discussion here on the differences:

http://www.mdbmakers.com/forums/showthread.php?t=2602

 

[pablofonto]

Paul, do you think if I add a "1" to all the blank fields in the Subty as a workaround would be a good idea? I can change the query to be a make table query and delete all the "1" after. Please let me know what you think, this is getting to my nerves already.

thanks,

Pablo

 

[pbaldy]

It's your data. That sounds like it would solve this problem, but only you know if it causes another. An alternative might be to run an update query to change Nulls to ZLS, then change the properties of that field to Reguired=Yes and Allow ZLS = Yes. I think that will make it so it won't allow Nulls in the future.

 

[pablofonto]

Yep, I actually already did use the update query to change all the blanks to "1" and once I got my final table I ran the update query again to make all thos fields NULL. I think this works out!

thanks for your help and have a nice weekend!

Pablo