asked on

Syntax error in insert into statement

I have the following sql, when I try to run it gave me "Syntax error in insert into statement.

Could someone see if they could find the problem

dim fSql as string

myDate = Nz(InputBox("Enter Date"), "")
myRange = (InputBox("Enter Range No."))

fSql = " INSERT INTO tblScoutRpt ( DatProcess, pID, IMITEM, IMSIZE, GENUS, SPECIS, IMDES3 "
fSql = fSql & " LRCODE, LRCSRC, LRAREA, HOUSE, BENCH, SOWD, INVQ, QTOH, RED, RDYD, EXPD "
fSql = fSql & " MVDT, GrnHouseOwner, Cult1, CultSeverity1, CT1, Cult2, CultSeverity2, CT2 "
fSql = fSql & " Cult3, CultSeverity3, CT3, Cult4, CultSeverity4, CT4, Disease1 "
fSql = fSql & " DiseaseSeverity1, DT1, Disease2, DiseaseSeverity2, DT2, Disease3 "
fSql = fSql & " DiseaseSeverity3, DT3, Disease4, DiseaseSeverity4, DT4, Pest1, PestStage1 "
fSql = fSql & " PestSeverity1, PT1, Pest2, PestStage2, PestSeverity2, PT2, Pest3 "
fSql = fSql & " PestStage3, PestSeverity3, PT3, Pest4, PestStage4, PestSeverity4, PT4 "
fSql = fSql & " MisComment, PlantGSV, Range ) "
fSql = fSql & " SELECT ([#" & myDate & "#] AS DatProcess, [pID] & (Format([DatProcess],#mmddyy#)) AS APpID "
fSql = fSql & " tblScout.IMITEM , tblScout.IMSIZE, tblScout.Genus, tblScout.SPECIS "
fSql = fSql & " tblScout.IMDES3, tblScout.LRCODE, tblScout.LRCSRC, tblScout.lrarea "
fSql = fSql & " tblScout.House, tblScout.BENCH, tblScout.sowd, tblScout.INVQ, tblScout.QTOH "
fSql = fSql & " tblScout.RED, tblScout.RDYD, tblScout.EXPD, tblScout.MVDT "
fSql = fSql & " tblScout.GrnHouseOwner, tblScout.cult1, tblScout.CultSeverity1 "
fSql = fSql & " tblScout.CT1, tblScout.Cult2, tblScout.CultSeverity2, tblScout.CT2 "
fSql = fSql & " tblScout.Cult3, tblScout.CultSeverity3, tblScout.CT3, tblScout.Cult4 "
fSql = fSql & " tblScout.CultSeverity4, tblScout.CT4, tblScout.Disease1 "
fSql = fSql & " tblScout.DiseaseSeverity1, tblScout.DT1, tblScout.Disease2 "
fSql = fSql & " tblScout.DiseaseSeverity2, tblScout.DT2, tblScout.Disease3 "
fSql = fSql & " tblScout.DiseaseSeverity3, tblScout.DT3, tblScout.Disease4 "
fSql = fSql & " tblScout.DiseaseSeverity4, tblScout.DT4, tblScout.Pest1, tblScout.PestStage1 "
fSql = fSql & " tblScout.PestSeverity1, tblScout.PT1, tblScout.Pest2, tblScout.PestStage2 "
fSql = fSql & " tblScout.PestSeverity2, tblScout.PT2, tblScout.Pest3, tblScout.PestStage3 "
fSql = fSql & " tblScout.PestSeverity3, tblScout.PT3, tblScout.Pest4, tblScout.PestStage4 "
fSql = fSql & " tblScout.PestSeverity4, tblScout.PT4, tblScout.MisComment, tblScout.PlantGSV, [myRange] AS ApRange) "
fSql = fSql & " FROM tblScout "
fSql = fSql & " WHERE (((tblScout.Cult1) Is Not Null)) OR (((tblScout.Disease1) Is Not Null)) OR (((tblScout.Pest1) Is Not Null)) "
CurrentDb.Execute fSql

Thanks you

Perennial

perkc

Your lines need ,'s:

fSql = " INSERT INTO tblScoutRpt ( DatProcess, pID, IMITEM, IMSIZE, GENUS, SPECIS, IMDES3 "
fSql = fSql & " LRCODE, LRCSRC, LRAREA, HOUSE, BENCH, SOWD, INVQ, QTOH, RED, RDYD, EXPD "

should be:

fSql = " INSERT INTO tblScoutRpt ( DatProcess, pID, IMITEM, IMSIZE, GENUS, SPECIS, IMDES3, "
fSql = fSql & " LRCODE, LRCSRC, LRAREA, HOUSE, BENCH, SOWD, INVQ, QTOH, RED, RDYD, EXPD, "

Repeat the comma's on all lines nessesary.

perkc

perkc

The total fix would be:

fSql = " INSERT INTO tblScoutRpt ( DatProcess, pID, IMITEM, IMSIZE, GENUS, SPECIS, IMDES3, "
fSql = fSql & " LRCODE, LRCSRC, LRAREA, HOUSE, BENCH, SOWD, INVQ, QTOH, RED, RDYD, EXPD, "
fSql = fSql & " MVDT, GrnHouseOwner, Cult1, CultSeverity1, CT1, Cult2, CultSeverity2, CT2, "
fSql = fSql & " Cult3, CultSeverity3, CT3, Cult4, CultSeverity4, CT4, Disease1, "
fSql = fSql & " DiseaseSeverity1, DT1, Disease2, DiseaseSeverity2, DT2, Disease3, "
fSql = fSql & " DiseaseSeverity3, DT3, Disease4, DiseaseSeverity4, DT4, Pest1, PestStage1, "
fSql = fSql & " PestSeverity1, PT1, Pest2, PestStage2, PestSeverity2, PT2, Pest3, "
fSql = fSql & " PestStage3, PestSeverity3, PT3, Pest4, PestStage4, PestSeverity4, PT4, "
fSql = fSql & " MisComment, PlantGSV, Range ) "
fSql = fSql & " SELECT ([#" & myDate & "#] AS DatProcess, [pID] & (Format([DatProcess],#mmddyy#)) AS APpID, "
fSql = fSql & " tblScout.IMITEM , tblScout.IMSIZE, tblScout.Genus, tblScout.SPECIS, "
fSql = fSql & " tblScout.IMDES3, tblScout.LRCODE, tblScout.LRCSRC, tblScout.lrarea, "
fSql = fSql & " tblScout.House, tblScout.BENCH, tblScout.sowd, tblScout.INVQ, tblScout.QTOH, "
fSql = fSql & " tblScout.RED, tblScout.RDYD, tblScout.EXPD, tblScout.MVDT, "
fSql = fSql & " tblScout.GrnHouseOwner, tblScout.cult1, tblScout.CultSeverity1, "
fSql = fSql & " tblScout.CT1, tblScout.Cult2, tblScout.CultSeverity2, tblScout.CT2, "
fSql = fSql & " tblScout.Cult3, tblScout.CultSeverity3, tblScout.CT3, tblScout.Cult4, "
fSql = fSql & " tblScout.CultSeverity4, tblScout.CT4, tblScout.Disease1, "
fSql = fSql & " tblScout.DiseaseSeverity1, tblScout.DT1, tblScout.Disease2, "
fSql = fSql & " tblScout.DiseaseSeverity2, tblScout.DT2, tblScout.Disease3, "
fSql = fSql & " tblScout.DiseaseSeverity3, tblScout.DT3, tblScout.Disease4, "
fSql = fSql & " tblScout.DiseaseSeverity4, tblScout.DT4, tblScout.Pest1, tblScout.PestStage1, "
fSql = fSql & " tblScout.PestSeverity1, tblScout.PT1, tblScout.Pest2, tblScout.PestStage2, "
fSql = fSql & " tblScout.PestSeverity2, tblScout.PT2, tblScout.Pest3, tblScout.PestStage3, "
fSql = fSql & " tblScout.PestSeverity3, tblScout.PT3, tblScout.Pest4, tblScout.PestStage4, "
fSql = fSql & " tblScout.PestSeverity4, tblScout.PT4, tblScout.MisComment, tblScout.PlantGSV, [myRange] AS ApRange) "
fSql = fSql & " FROM tblScout "
fSql = fSql & " WHERE (((tblScout.Cult1) Is Not Null)) OR (((tblScout.Disease1) Is Not Null)) OR (((tblScout.Pest1) Is Not Null)) "

perkc

perennial

ASKER

It seems working but, it's not appending to my other table "tblScoutRpt".

ASKER CERTIFIED SOLUTION

perkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

The second field of the insert clause is pID but the second field of the select clause is APpID.