Tracking down "Invalid use of Null" in Find Unmatched Query

[catluvr]

I have a query that returns "Invalid use of Null" and I can't for the life of me figure out the issue! I have other queries that find unmatched records and they work just fine. I finally copied the SQL from a working query to Word and compared it to the non-working query and I STILL can't find the problem.



What I'm trying to do is compare my database with a spreadsheet that I receive monthly.

The query that pulls the info from my database records is [qry TL Database]. It has five fields:

TrpNum
TrpPos
FirstName
LastName
LeaderID <-- this is a calculated field that is [TrpNum] & [FirstName] & [LastName]

I created the LeaderID field because it's possible for an adult to be a troop leader for two different troops.

This query works fine.

The query that pulls the info from the spreadsheet records is [qry TL CurrentSpreadsheet]. It has six fields:

Troop/Group (this is a text field that is formatted "Troop 00000")
Position
First Name
Last Name
TrpNum <-- this is a calculated field that is CLng(Right([Troop/Group],5)) <-- this is to convert the Troop/Group to an actual number
LeaderID <-- this is a calculated field that is [TrpNum] & [First Name] & [Last Name]

This query also works fine.

I want to return records from [qry TL Database] that have no matching records in [qry TL CurrentSpreadsheet] based on LeaderID.

I was pretty sure I knew how to do this, but I kept getting the "Invalid use of Null" error so I decided to use the Query Wizard. That returned the same error.

Here is the SQL that was created by the Query Wizard (which is identical to the SQL that was created when I created the query "from scratch")

SELECT [qry TL Database].TrpNum, [qry TL Database].FirstName, [qry TL Database].LastName, [qry TL Database].LeaderID
FROM [qry TL Database] LEFT JOIN [qry TL CurrentSpreadsheet] ON [qry TL Database].[LeaderID] = [qry TL CurrentSpreadsheet].[LeaderID]
WHERE ((([qry TL CurrentSpreadsheet].LeaderID) Is Null));

If I remove the WHERE clause and add in [qry TL CurrentSpreadsheet].[LeaderID] to the SELECT clause, then the query runs and where ever there isn't a record in [qry TL CurrentSpreadsheet], the datasheet displays "#Error" in the [qry TL CurrentSpreadsheet].[LeaderID] field.

So, what the heck am I doing wrong?? My hope was to also create the reverse, a query that shows records that aren't in the database but are in the spreadsheet.

Thanks in advance for the help.

Susie
Kansas

[isladogs]

I don't believe you can do this on a calculated field though as I never use them, I may be wrong.
Try having the three individual field as Is Null instead

[catluvr]

So, thinking that Colin may be on to something, I created two new queries. Each is based on the original queries, but now don't have any calculated fields. They both have a troop number, first name, last name, and leaderID.

But, I got the same error.

I don't understand:

"Try having the three individual field as Is Null instead"

How do I check to see if all three fields match?

I can't just check on last name because some adults are troop leaders for more than one troop.

Susie

[isladogs]

Busy doing other things at the moment!
Its obviously far easier if you have a sinlge PK field to check but the process also works for multiple fields
Have a look at my website article on Synchronising Data - http://www.mendipdatasystems.co.uk/s...a-1/4594514001
Its in 2 parts - the second part may be more relevant to you

If nothing there helps, have a look at the example apps in this post: https://www.access-programmers.co.uk...9&postcount=68
The code was developed by AWF member MajP as part of this very lengthy thread. You may need to skim the entire thread to understand the point of it

[isladogs]

Back again. All I meant was something like this

[catluvr]

Okay, I got it figured out! Thank you Colin/IslaDogs!

I stopped using the Wizard, I stopped using the calculated field, and I joined the Troop number, first name, and last name with Left Joins.

Works fine! I guess it just didn't occur to me to use the triple join.

I now have a union query that lets me know when the database records and the spreadsheet records don't agree.

Again, thank you!

Susie
Kansas

[isladogs]

You're welcome. Glad I could help