Simple = (IIF) statement returns error?

[Runawaygeek]

I have 2 queries, on each one sits a string column called Package_ID.

I have created a LEFT JOIN between the 2, (all Items on Q1 and only matched Items on Q2)

My Statement is

IFF([Q1].[PackageID] = [Q2].[PackageID], "Match", "NO Match")

I expect where the IDs match to return MATCH (This happens) and where no matching ID is found to return NO Match, but i get #Error for the No Match?

There are no Nulls, as i have removed all of them in previous queries.
IDs are around 10Char maybe 15 Max.
They are String values.

Thank you for any help

 

[CJ_London]

have you freetyped your code or copy pasted? it is IIF, not IFF

If there are no nulls then why use a left join? A left join will generate nulls if there is no matching record in Q2

you could try

iif(Q2.packageID is null,"No Match","Match")

 

[Runawaygeek]

I free typed, its IIF.

I can only choose from Inner, Left or Right Joins in the Query builder.

With Inner Join it only returns the rows where there is an ID Match.
But i need to return the rows with no match and identify them.

I thought LEFT would return all the rows in Q1 and mark "No Match" in the column.

I have even tried it as SQL:

SELECT
Q1.COL1
,Q1.COL2
,Q1.COL3
,Q1.PackageID
, IIF(NEWQ.ID IS NULL, "NO MATCH", "MATCH") AS ID_MATCH 

FROM Q1
LEFT JOIN (SELECT Q2.PackageID FROM Q2) NEWQ ON Q1.ID = NEWQ.ID

But either way i still get #Error for all the "NO MATCH" results, Match still works fine :-S

Cheers

 

[CJ_London]

you are making it a lot more complicated than it needs to be

 SELECT
Q1.COL1
,Q1.COL2
,Q1.COL3
,Q1.PackageID
, IIF(Q2.ID IS NULL, "NO MATCH", "MATCH") AS ID_MATCH 
FROM Q1
LEFT JOIN Q2 ON Q1.ID = Q2.ID

or just

SELECT
Q1.COL1
,Q1.COL2
,Q1.COL3
,Q1.PackageID
FROM Q1
LEFT JOIN Q2 ON Q1.ID = Q2.ID
WHERE Q2.ID is null

 

[Runawaygeek]

I know, the sub-select was an effort to and force the null values from Q2.
Either way, it's still returning #Error and I have no idea why.

Would it be to do with the fact I am joining on the data I am returning?
I don't see why, but I am out of ideas now!

I really hate access sometimes!

 

[CJ_London]

1. do you read the threads posted back?
2. Do you try them?
3. Do you have any feedback - do they work/not work why/why not?

and if you are freetyping - why not just copy and paste, all you are doing is introducing unnecessary errors

 

[Runawaygeek]

Sorry maybe I am confusing things, I only free type on this forum, everything else uses the tools and features in access, I don't write the SQL unless I have too.

I have tried all the solutions in this thread, all produce the same result.

The syntax is correct the error must be of logic. But I can't work out where.

 

[CJ_London]

The examples in post #4 both work, so my only other thought is there is a problem with the Q2.

Good luck with your project

 

[Runawaygeek]

Thanks, I still cant work it out, its really weird.

I built 2 new tables of FAKE data and rebuilt the query and the joins and it works fine. So somewhere is an issue, but i cant work out what.

The only difference, in my test one, they were tables not Queries, so i might append a table and run it on that..

I wish someone would buy me an Oracle server :-D

 

[Minty]

As CJ stated - It sounds as if your queries aren't producing the results you think they are. Are you getting "" empty string values perhaps instead of nulls.

 

[Runawaygeek]

They are not, all this time i have only looked at the columns that i am comparing. But when i decided to Append the query to a table the process informed me of 20 cells that were screwed, turns out they were all data-formats on another column where the user had put them in wrong (or as text or something) A quick, clear up and BOOM its working!

So a top tip to all getting an error like this, Append the Query to a table and it will swap your "crap" data for NULL and allow you to find your problem!

Thanks all for the advice through the thinking process