SQL syntax error

[max]

Hi I am getting the following error upon running some SQL in VBA

Run-time error '3075':

Syntax error (missing operator) in query expression
'CurrentConfigurationQry.EvaseDiameterFROM (((FanIDTbl LEFT JOIN PrimaryLocationsQry ON FanIDTbl.FanIndexID = PrimaryLocationsQry.FanIndexID)LEFT JOIN PrimaryFanLocationsTbl ON (PrimaryLocationsQry.MaxOfDateOfMove = PrimaryFanLocationsTbl.DateOfMove) AND (PrimaryLocationsQry.FanIndexID =


The code works in the query builder from where it was copied - the only modification has been the insertion of line breaks. The full SQL code is as follows:




Set Rst2 = CurrentDb.OpenRecordset("SELECT FanIDTbl.FanIndexID, FanIDTbl.FanName, FanIDTbl.Type, PrimaryFanLocationsTbl.LocationID, PrimaryLocationsTbl.DuctDiameter, PrimaryLocationsTbl.TestPointElevation, PrimaryLocationsTbl.Location, PrimaryLocationsQry.MaxOfDateOfMove, CurrentConfigurationQry.MaxOfConfigurationDate, CurrentConfigurationQry.Solidity, CurrentConfigurationQry.NumberOfBlades, CurrentConfigurationQry.BladePitch, CurrentConfigurationQry.EvaseDiameter" & _

"FROM (((FanIDTbl LEFT JOIN PrimaryLocationsQry ON FanIDTbl.FanIndexID = PrimaryLocationsQry.FanIndexID)" & _

"LEFT JOIN PrimaryFanLocationsTbl ON (PrimaryLocationsQry.MaxOfDateOfMove = PrimaryFanLocationsTbl.DateOfMove) AND (PrimaryLocationsQry.FanIndexID = PrimaryFanLocationsTbl.FanIndexID))" & _

"LEFT JOIN PrimaryLocationsTbl ON PrimaryFanLocationsTbl.LocationID = PrimaryLocationsTbl.LocationID)" & _

"LEFT JOIN CurrentConfigurationQry ON PrimaryLocationsQry.FanIndexID = CurrentConfigurationQry.FanIndexID" & _

"WHERE (((PrimaryLocationsTbl.Location)=[LocationName]) AND ((FanIDTbl.Current)=Yes) AND ((FanIDTbl.PrimaryFan)=Yes));", dbOpenSnapshot)


Any help is much appreciated

[June7]

Need to have spaces in the SQL string so things don't run together when it compiles, as you can see is happening in the error message.

" FROM ...

" LEFT ...

" LEFT ...

" LEFT ...

" WHERE ...

[max]

Hi, making progress -the syntax error is smaller now

Run-time error '3075':

Syntax error (missing operator) in query expression
'(((PrimaryLocationsTbl.Location)=South Exhust) AND
((FanIDtbl.Current)=Yes) AND ((FanIDTbl.PrimaryFan)=Yes);',

The code has been modified to

Set Rst2 = CurrentDb.OpenRecordset("SELECT FanIDTbl.FanIndexID, FanIDTbl.FanName, FanIDTbl.Type, PrimaryFanLocationsTbl.LocationID, PrimaryLocationsTbl.DuctDiameter, PrimaryLocationsTbl.TestPointElevation, PrimaryLocationsTbl.Location, PrimaryLocationsQry.MaxOfDateOfMove, CurrentConfigurationQry.MaxOfConfigurationDate, CurrentConfigurationQry.Solidity, CurrentConfigurationQry.NumberOfBlades, CurrentConfigurationQry.BladePitch, CurrentConfigurationQry.EvaseDiameter " & _
" FROM (((FanIDTbl LEFT JOIN PrimaryLocationsQry ON FanIDTbl.FanIndexID = PrimaryLocationsQry.FanIndexID) " & _
" LEFT JOIN PrimaryFanLocationsTbl ON (PrimaryLocationsQry.MaxOfDateOfMove = PrimaryFanLocationsTbl.DateOfMove) AND (PrimaryLocationsQry.FanIndexID = PrimaryFanLocationsTbl.FanIndexID)) " & _
" LEFT JOIN PrimaryLocationsTbl ON PrimaryFanLocationsTbl.LocationID = PrimaryLocationsTbl.LocationID) " & _
" LEFT JOIN CurrentConfigurationQry ON PrimaryLocationsQry.FanIndexID = CurrentConfigurationQry.FanIndexID " & _
" WHERE (((PrimaryLocationsTbl.Location)=" & LocationName & ") AND ((FanIDTbl.Current)=Yes) AND ((FanIDTbl.PrimaryFan)=Yes);", dbOpenSnapshot)

[pbaldy]

Try

" WHERE PrimaryLocationsTbl.Location='" & LocationName & "' AND FanIDTbl.Current=Yes AND FanIDTbl.PrimaryFan=Yes;", dbOpenSnapshot)

[max]

Success!! Thanks very much