SQL in VB Code

  • Thread starter Thread starter T Best
  • Start date Start date
T

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
 
T Best said:
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
Click to expand...

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) "
Click to expand...

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