Why am I getting a syntax error?

[emorris1000]

I'm getting a syntax error on some SQL that is throwing me for a bit of a loop.

This code throws a compile syntax error:

Me.lbSearch.RowSource = "Select [General Data].PolyID, [General Data].SupCatID, [General Data].C6C2RatTarg, [General Data].H2C2RatTarg, [General Data].Productivity, IIf([TGPCGeneral]![PolyID] Is Not Null,"X","") AS GPC " _
& "From [General Data] " _
& "LEFT JOIN tGPCGeneral ON [General Data].PolyId = tGPCGeneral.PolyID " _
& "WHERE ([General Data].PolyID like ""*"" & Forms!fSearchPoly!tbPolyID & ""*"" OR Forms!fsearchPoly!tbPolyID is null) " _
& "AND ([General Data].SupCatID like ""*"" & Forms!fsearchPoly!tbSupCatID & ""*"" OR Forms!fsearchPoly!tbSupCatID is null) " _
& "AND ([General Data].ReactorID like Forms!fSearchPoly!cbReactorID OR Forms!fSearchPoly!cbReactorID is Null) " _
& "AND (([General Data].C6C2RatTarg > Forms!fSearchPoly!tbC6C2Min OR Forms!fSearchPoly!tbC6C2Min is Null) AND ([General Data].C6C2RatTarg < Forms!fSearchPoly!tbC6C2Max OR Forms!fSearchPoly!tbC6C2Max is Null)) " _
& "AND (([General Data].H2C2RatTarg > Forms!fSea

Yet, this code works fine:

Me.lbSearch.RowSource = "Select [General Data].PolyID, [General Data].SupCatID, [General Data].C6C2RatTarg, [General Data].H2C2RatTarg, [General Data].Productivity, tGPCGeneral.Mw " _
& "From [General Data] " _
& "LEFT JOIN tGPCGeneral ON [General Data].PolyId = tGPCGeneral.PolyID " _
& "WHERE ([General Data].PolyID like ""*"" & Forms!fSearchPoly!tbPolyID & ""*"" OR Forms!fsearchPoly!tbPolyID is null) " _
& "AND ([General Data].SupCatID like ""*"" & Forms!fsearchPoly!tbSupCatID & ""*"" OR Forms!fsearchPoly!tbSupCatID is null) " _
& "AND ([General Data].ReactorID like Forms!fSearchPoly!cbReactorID OR Forms!fSearchPoly!cbReactorID is Null) " _
& "AND (([General Data].C6C2RatTarg > Forms!fSearchPoly!tbC6C2Min OR Forms!fSearchPoly!tbC6C2Min is Null) AND ([General Data].C6C2RatTarg < Forms!fSearchPoly!tbC6C2Max OR Forms!fSearchPoly!tbC6C2Max is Null)) " _
& "AND (([General Data].H2C2RatTarg > Forms!fSearchPoly!tbH2C2Min OR Forms!fSearchPoly!tbH2C2Min is Null) AND ([General Data].H2C2RatTarg < Forms!fSearchPoly!tbH2C2Max OR Forms!fSearchPoly!tbH2C2Max is Null)) " _
& "ORDER BY [General Data].PolyID;"

The only difference is that I changed the IIF in the SELECT to a specific field. Now, I've used this IIF statement before and it works fine. Any ideas what's going on? Also, just as a note, I can't put this code into the query design window due to the logical stuff in the bottom, so I'm having to debug this manually.

 

[pbaldy]

The second doesn't have an IIf(), and the double quotes in the failing one would cause a problem (the first set there terminates the SQL string). Change those to single quotes and see what happens.

Presumably a cut/paste error at the end?

 

[kevlray]

Either you mis-copied the first query or you are missing some of the where clause.

I have not done NULL checks in Access before, so I am assuming it is correct. If polyID is a string, you may try len([TGPCGeneral]![PolyID]) <>0 in your iif statement.

 

[emorris1000]

The second doesn't have an IIf(), and the double quotes in the failing one would cause a problem (the first set there terminates the SQL string). Change those to single quotes and see what happens.

Presumably a cut/paste error at the end?

Its got to be the quotes. Can't believe I missed that :banghead:. You sir are a gentleman and a scholar. Pip pip.

ed: I just switched it, it was the quotes.

Either you mis-copied the first query or you are missing some of the where clause.

I have not done NULL checks in Access before, so I am assuming it is correct. If polyID is a string, you may try len([TGPCGeneral]![PolyID]) <>0 in your iif statement.

I miscopied it, everything below the first line was the same for both queries.

 

[pbaldy]

You sir are a gentleman and a scholar. Pip pip.

I have a number of former teachers who would dispute the second point.

Glad it sorted out the problem.