How to fix "Compile Error Expected: end of statement" in a query?

[engrlorie]

I am a newbie in programing and is currently designing a program using VB6 with access 03 database. Unfortunately, I am stuck with the error. Please help me.. can't decipher the problem myself. Scenario is I have 4 tables (lightinfo, location, characteristics, and pics). They are connected through National ID from lightinfo.

strQuery = "SELECT LIGHTINFO.info_NationalNo, LIGHTINFO.info_InternationalNo, LIGHTINFO.info_namriaName, " & _
"LIGHTINFO.info_cgNo, LIGHTINFO.info_cgName, LIGHTINFO.info_Category, LIGHTINFO.info_YearEstablished, " & _
"LIGHTINFO.info_Chart Affected, LIGHTINFO.info_Comparison, LIGHTINFO.info_CGStation, LIGHTINFO.info_Authority, " & _
"LIGHTINFO.info_LotOwner, LIGHTINFO.info_LightKeeper, LIGHTINFO.info_Remark, LOCATION.loc_Region, LOCATION.loc_Province, " & _
"LOCATION.loc_MunCity, LOCATION.loc_Barangay, LOCATION.loc_LatPosition, LOCATION.loc_LongPosition, LOCATION.loc_PosAccuracy, " & _
"LOCATION.loc_Datum, CHARACTERISTICS.c_CharType, CHARACTERISTICS.c_LantColor, CHARACTERISTICS.c_Period, CHARACTERISTICS.c_Elevation, " & _
"CHARACTERISTICS.c_Range, CHARACTERISTICS.c_StrucType, CHARACTERISTICS.c_StrucColor, CHARACTERISTICS.c_StrucHeight, " & _
"CHARACTERISTICS.c_DisplayStatus, CHARACTERISTICS.c_VisAnge, CHARACTERISTICS.c_VisArc, CHARACTERISTICS.c_FlashPhase, " & _
"CHARACTERISTICS.c_IntensifiedColor, PICS.Pic_Image, PICS.Pic_Source " & _
"FROM ((LIGHTINFO INNER JOIN CHARACTERISTICS ON LIGHTINFO.info_NationalNo = CHARACTERISTICS.c_NationalNo) " & _
 "INNER JOIN LOCATION ON LIGHTINFO.info_NationalNo = LOCATION.loc_NationalNo) & _
"INNER JOIN PICS ON LIGHTINFO.info_NationalNo = PICS.Pic_NationalNo"
 

[Stewart Ross]

It's easy to overlook a double quote to end a substring in a complex VB-built SQL statement like this. The first Inner Join (line 11) is missing its ending double-quote, as shown below. The corrected version is also shown.

                                                                            v
"INNER JOIN LOCATION ON LIGHTINFO.info_NationalNo = LOCATION.loc_NationalNo) & _
                                                                            ^  
 
"INNER JOIN LOCATION ON LIGHTINFO.info_NationalNo = LOCATION.loc_NationalNo)" & _ 

-Stewart

[engrlorie]

thanks... so clumsy of me.