SQL in VB Code

[T Best]

Hi All,
I was wondering if someone could take a quick look at this and see where my
syntax is wrong. I have a very long SQL statement which can't fit on one
line so i split it up. I've never done this before. When I look in the debug
window the value of strSQL looks ok but Access 97 is giving me a "Syntax
error in INSERT INTO statement." Any help would be very much appreciated.

TIA
Ted

strSQL = "INSERT INTO tblSNMain (InsuredName, TradingAs, InsuredAddress1,
InsuredAddress2, InsuredCity, InsuredState, InsuredZip, BusDesc, BrokerCode,
BrokerName, BrokerAddress1, BrokerAddress2, BrokerCity, BrokerState,
BrokerZip, InsCo, PolNumber, PolType, PolEffDt, PolExpDt, DedEachOcc,
EnhanceEndDeductible, WindDeductible, ReplacementCostValue, ActualCashValue,
GLGenAggregate, GLProdCompOpsAgg, GLPersonal_AdvInjury, GLEachOccLimit,
GLFireDamageLimit, GLMedicalExpenseLimit, Exposure, Rate, Premium,
Occupancy, LLOccurence, LLAggregate, DescOfOperation, LLPremium, LLRate,
LLReceipts, NOAEachOcc, Symbol, PolPremium, PolTax, PolSurcharge,
FI-SubDivision, FI-NameOfPerson, FI-ProtectiveSafeguardDesc,
FI-TerrorismDate, FI-TerrorismPrem, FI-EstimatedDate, FI-EstimatedPrem,
FI-TerrorismPremCertAcct, FI-TerrorismPrem_Notice, FI-FireFollowingPrem,
FI-MVLiabilityInsPrem, PolicyDescription)"

strSQL = strSQL & "SELECT InsuredName, TradingAs, InsuredAddress1,
InsuredAddress2, InsuredCity, InsuredState, InsuredZip, BusDesc, BrokerCode,
BrokerName, BrokerAddress1, BrokerAddress2, BrokerCity, BrokerState,
BrokerZip, InsCo, PolNumber, PolType, PolEffDt, PolExpDt, DedEachOcc,
EnhanceEndDeductible, WindDeductible, ReplacementCostValue, ActualCashValue,
GLGenAggregate, GLProdCompOpsAgg, GLPersonal_AdvInjury, GLEachOccLimit,
GLFireDamageLimit, GLMedicalExpenseLimit, Exposure, Rate, Premium,
Occupancy, LLOccurence, LLAggregate, DescOfOperation, LLPremium, LLRate,
LLReceipts, NOAEachOcc, Symbol, PolPremium, PolTax, PolSurcharge,
FI-SubDivision, FI-NameOfPerson,"

strSQL = strSQL & "FI-ProtectiveSafeguardDesc, FI-TerrorismDate,
FI-TerrorismPrem, FI-EstimatedDate, FI-EstimatedPrem,
FI-TerrorismPremCertAcct, FI-TerrorismPrem_Notice, FI-FireFollowingPrem,
FI-MVLiabilityInsPrem, PolicyDescription From tblSNMain WHERE ID = " &
tempID

 

[Dirk Goldgar]

Hi All,
I was wondering if someone could take a quick look at this and see
where my syntax is wrong. I have a very long SQL statement which
can't fit on one line so i split it up. I've never done this before.
When I look in the debug window the value of strSQL looks ok but
Access 97 is giving me a "Syntax error in INSERT INTO statement." Any
help would be very much appreciated.

TIA
Ted

strSQL = "INSERT INTO tblSNMain (InsuredName, TradingAs,
InsuredAddress1, InsuredAddress2, InsuredCity, InsuredState,
InsuredZip, BusDesc, BrokerCode, BrokerName, BrokerAddress1,
BrokerAddress2, BrokerCity, BrokerState, BrokerZip, InsCo, PolNumber,
PolType, PolEffDt, PolExpDt, DedEachOcc, EnhanceEndDeductible,
WindDeductible, ReplacementCostValue, ActualCashValue,
GLGenAggregate, GLProdCompOpsAgg, GLPersonal_AdvInjury,
GLEachOccLimit, GLFireDamageLimit, GLMedicalExpenseLimit, Exposure,
Rate, Premium, Occupancy, LLOccurence, LLAggregate, DescOfOperation,
LLPremium, LLRate, LLReceipts, NOAEachOcc, Symbol, PolPremium,
PolTax, PolSurcharge, FI-SubDivision, FI-NameOfPerson,
FI-ProtectiveSafeguardDesc, FI-TerrorismDate, FI-TerrorismPrem,
FI-EstimatedDate, FI-EstimatedPrem, FI-TerrorismPremCertAcct,
FI-TerrorismPrem_Notice, FI-FireFollowingPrem, FI-MVLiabilityInsPrem,
PolicyDescription)"

strSQL = strSQL & "SELECT InsuredName, TradingAs, InsuredAddress1,
InsuredAddress2, InsuredCity, InsuredState, InsuredZip, BusDesc,
BrokerCode, BrokerName, BrokerAddress1, BrokerAddress2, BrokerCity,
BrokerState, BrokerZip, InsCo, PolNumber, PolType, PolEffDt,
PolExpDt, DedEachOcc, EnhanceEndDeductible, WindDeductible,
ReplacementCostValue, ActualCashValue, GLGenAggregate,
GLProdCompOpsAgg, GLPersonal_AdvInjury, GLEachOccLimit,
GLFireDamageLimit, GLMedicalExpenseLimit, Exposure, Rate, Premium,
Occupancy, LLOccurence, LLAggregate, DescOfOperation, LLPremium,
LLRate, LLReceipts, NOAEachOcc, Symbol, PolPremium, PolTax,
PolSurcharge, FI-SubDivision, FI-NameOfPerson,"

strSQL = strSQL & "FI-ProtectiveSafeguardDesc, FI-TerrorismDate,
FI-TerrorismPrem, FI-EstimatedDate, FI-EstimatedPrem,
FI-TerrorismPremCertAcct, FI-TerrorismPrem_Notice,
FI-FireFollowingPrem, FI-MVLiabilityInsPrem, PolicyDescription From
tblSNMain WHERE ID = " & tempID

Those fields that have the hyphen/minus sign (-) in their names must
have their names surrounded by square brackets ([]); e.g.,
"[FI-SubDivision]". Otherwise it's going to look to the SQL parser as
if you're trying to insert an expression (something minus something
else) into an expression.

I'd also recommend that incorporate a space at the end of your first
long string literal:

....

FI-TerrorismPrem_Notice, FI-FireFollowingPrem, FI-MVLiabilityInsPrem,
PolicyDescription) "

I believe the statement will still be parsed correctly without that, but
it would make it clearer to read.