Solved

Syntax error in insert into statement

Posted on 2004-10-27
246 Views
Last Modified: 2008-03-10
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
0
Question by:perennial
    5 Comments
     
    LVL 10

    Expert Comment

    by: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
    0
     
    LVL 10

    Expert Comment

    by: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
    0
     
    LVL 1

    Author Comment

    by:perennial
    It seems working but, it's not appending to my other table "tblScoutRpt".
    0
     
    LVL 10

    Accepted Solution

    by:
    Does the following sql return any records?

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

    I'm guessing that it doesn't. I think the problem is with the following line:

    fSql = fSql & " SELECT ([#" & myDate & "#] AS DatProcess, [pID] & (Format([DatProcess],#mmddyy#)) AS APpID, "

    Try:
    fSql = fSql & " SELECT ([#" & myDate & "#] AS DatProcess, [pID] & (Format(#" & myDate & "#,#mmddyy#)) AS APpID, "

    Also, where is the [pID] comming from? Is it a field in the tblScout table? a variable?

    perkc

    0
     
    LVL 44

    Expert Comment

    by:GRayL
    The second field of the insert clause is pID but the second field of the select clause is  APpID.  
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    845 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now