• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Access SQL Syntax error

Hi

I have made a syntax error in the following Access SQL build. Can't spot it
Dim S As String
S = "INSERT INTO Performance ([Date], [Entered_By], Foreman, Supervisor, Shaft, Machine_Number, Hole_Number, Site, Shift, Managing_Operator, Assistant_Operator, Assistant_Operator2, Drilled_From, Drilled_To, AXT, BX, NX, Other, Drilled_Total, Rock_Redrill, Concrete_Redrill, Drill_Hours, Travel_Hours, Transport_Hours, Lesedi_Delays, Mine_Delays, Grout_Hours, Survey_Hours, Machine_Maintain, DWR_Hours, Total_Hours, Remarks ) " _
& "SELECT " & "#" & CDate(oDate) & "# AS oDate, " _
& "'" & oEnteredBy & "', " & "'" & oForeman & "', " & "'" & oSupervisor & "', " & "'" & oShaft & "', " & "'" & oMachineNumber & "', " & "'" & oHoleNumber & "', " & "'" & oSite & "', " & "'" & oShift & "', " _
& "'" & oManagingOperator & "'," & "'" & oAssistantOperator & "'," & "'" & oAssistantOperator2 & "'," _
& oDrilledFrom & ", " _
& oDrilledTo & ", " _
& oAXT & ", " _
& oBX & ", " _
& oNX & ", " _
& oOther & ", " _
& DrilledTotal & "," _
& oRockRedrill & ", " _
& oConcreteRedrill & ", " _
& oDrillHours & ", " _
& oTravelHours & ", " _
& oTransportHours & ", " _
& oLesediDelays & ", " _
& oMineDelays & ", " _
& oGroutHours & ", " _
& oSurveyHours & ", " _
& oMachineMaintain & ", " _
& oDWRHours & ", " _
& oTotalHours & ", " _
& "'" & Remarks & "'"

Open in new window

0
Murray Brown
Asked:
Murray Brown
  • 5
  • 2
  • 2
  • +2
1 Solution
 
Michael VasilevskySolutions ArchitectCommented:
do a debug.print S and paste the resulting SQL into a query design view. Should be able to find it that way.
0
 
omgangIT ManagerCommented:
FROM????
You're select statement doesn't have a FROM part.
OM Gang
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
You don't need a FROM in an Insert SQL statement
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
SharathData EngineerCommented:
Then, from where are you getting the data for oDate, oEnteredBy  columns in the SELECT clause?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Here's the full set of code
Sub A()

    Dim oDate As String
    Dim oEnteredBy As String
    Dim oForeman As String
    Dim oSupervisor As String
    Dim oShaft As String
    Dim oMachineNumber As String
    Dim oHoleNumber As String
    Dim oSite As String
    Dim oShift As String
    Dim oManagingOperator As String
    Dim oAssistantOperator As String
    Dim oAssistantOperator2 As String
    Dim oDrilledFrom As String
    Dim oDrilledTo As String
    Dim oAXT As String
    Dim oBX As String
    Dim oNX As String
    Dim oOther As String
    Dim oDrilledTotal As String
    Dim oRockRedrill As String
    Dim oConcreteRedrill As String
    Dim oDrillHours As String
    Dim oTravelHours As String
    Dim oTransportHours As String
    Dim oLesediDelays As String
    Dim oMineDelays As String
    Dim oGroutHours As String
    Dim oSurveyHours As String
    Dim oMachineMaintain As String
    Dim oDWRHours As String
    Dim oTotalHours As String
    Dim oRemarks As String
   
    oDate = "2012/05/20"
    oEnteredBy = "06040"
    oForeman = "06050"
    oSupervisor = "Mr Supervisor"
    oShaft = "shaft 8gn"
    oMachineNumber = "I0/905"
    oHoleNumber = "ss 4d34"
    oSite = "a site"
    oShift = "n"
    oManagingOperator = "07890"
    oAssistantOperator = "09888"
    oAssistantOperator2 = "07845"
    oDrilledFrom = "12"
    oDrilledTo = "80"
    oAXT = "90"
    oBX = "5"
    oNX = "7"
    oOther = "9"
    oDrilledTotal = "89"
    oRockRedrill = "7.89"
    oConcreteRedrill = "90.9"
    oDrillHours = "67.8"
    oTravelHours = "8.8"
    oTransportHours = "6.3"
    oLesediDelays = "8.07"
    oMineDelays = "7.9"
    oGroutHours = "8.03"
    oSurveyHours = "8.33"
    oMachineMaintain = "3.02"
    oDWRHours = "7"
    oTotalHours = "67.89"
    oRemarks = "78.02"



    Call AppendPerformance(oDate, oEnteredBy, oForeman, oSupervisor, oShaft, oMachineNumber, oHoleNumber, oSite, oShift, oManagingOperator, oAssistantOperator, oAssistantOperator2, oDrilledFrom, _
    oDrilledTo, oAXT, oBX, oNX, oOther, oDrilledTotal, oRockRedrill, oConcreteRedrill, oDrillHours, oTravelHours, oTransportHours, oLesediDelays, oMineDelays, oGroutHours, oSurveyHours _
    , oMachineMaintain, oDWRHours, oTotalHours, oRemarks)

End Sub



Sub AppendPerformance(oDate As String, oEnteredBy As String, oForeman As String, oSupervisor As String, oShaft As String, oMachineNumber As String, oHoleNumber As String, oSite As String, _
oShift As String, oManagingOperator As String, oAssistantOperator As String, oAssistantOperator2 As String, oDrilledFrom As String, oDrilledTo As String _
   , oAXT As String, oBX As String, oNX As String, oOther As String, oDrilledTotal As String, oRockRedrill As String, oConcreteRedrill As String, oDrillHours As String, _
   oTravelHours As String, oTransportHours As String, oLesediDelays As String, oMineDelays As String, oGroutHours As String, oSurveyHours As String _
    , oMachineMaintain As String, oDWRHours As String, oTotalHours As String, oRemarks As String)


On Error GoTo EH

'Certain values won't go in so need to make sure you replace them with a space or zero
If oDrilledFrom = "" Then oDrilledFrom = "0.00"
If oDrilledTo = "" Then oDrilledTo = "0.00"
If oAXT = "" Then oAXT = "0.00"
If oBX = "" Then oBX = "0.00"
If oNX = "" Then oNX = "0.00"
If oOther = "" Then oOther = "0.00"
If oDrilledTotal = "" Then oDrilledTotal = "0.00"
If oRockRedrill = "" Then oRockRedrill = "0.00"
If oConcreteRedrill = "" Then oConcreteRedrill = "0.00"
If oDrillHours = "" Then oDrillHours = "0.00"
If oTravHours = "" Then oTravHours = "0.00"
If oTransportHours = "" Then oTransportHours = "0.00"
If oLesediDelays = "" Then oLesediDelays = "0.00"
If oMineDelays = "" Then oMineDelays = "0.00"
If oGroutHours = "" Then oGroutHours = "0.00"
If oSurveyHours = "" Then oSurveyHours = "0.00"
If oMachineMaintain = "" Then oMachineMaintain = "0.00"
If oDWRHours = "" Then oDWRHours = "0.00"
If oTotalHours = "" Then oTotalHours = "0.00"
If oRemarks = "" Then oRemarks = "0.00"


Dim S As String
S = "INSERT INTO Performance ([Date], [Entered_By], Foreman, Supervisor, Shaft, Machine_Number, Hole_Number, Site, Shift, Managing_Operator, Assistant_Operator, Assistant_Operator2, Drilled_From, Drilled_To, AXT, BX, NX, Other, Drilled_Total, Rock_Redrill, Concrete_Redrill, Drill_Hours, Travel_Hours, Transport_Hours, Lesedi_Delays, Mine_Delays, Grout_Hours, Survey_Hours, Machine_Maintain, DWR_Hours, Total_Hours, Remarks ) " _
& "SELECT " & "#" & CDate(oDate) & "# AS oDate, " _
& "'" & oEnteredBy & "', " & "'" & oForeman & "', " & "'" & oSupervisor & "', " & "'" & oShaft & "', " & "'" & oMachineNumber & "', " & "'" & oHoleNumber & "', " & "'" & oSite & "', " & "'" & oShift & "', " _
& "'" & oManagingOperator & "'," & "'" & oAssistantOperator & "'," & "'" & oAssistantOperator2 & "'," _
& oDrilledFrom & ", " _
& oDrilledTo & ", " _
& oAXT & ", " _
& oBX & ", " _
& oNX & ", " _
& oOther & ", " _
& DrilledTotal & "," _
& oRockRedrill & ", " _
& oConcreteRedrill & ", " _
& oDrillHours & ", " _
& oTravelHours & ", " _
& oTransportHours & ", " _
& oLesediDelays & ", " _
& oMineDelays & ", " _
& oGroutHours & ", " _
& oSurveyHours & ", " _
& oMachineMaintain & ", " _
& oDWRHours & ", " _
& oTotalHours & ", " _
& "'" & Remarks & "'"

Debug.Print S


Exit Sub

DoCmd.RunSQL S

Exit Sub
0
 
omgangIT ManagerCommented:
Yes....Insert Into ...... Values(x, y, z).  But you have a select statement and we all want to know what you are selecting from.  Perhaps you meant to/should use a
Select Into ....  Values (x, y, z) query instead.
OM Gang
0
 
GRayLCommented:
In addition, the closing parenthesis following Remarks in the INSERT INTO section.  
0
 
GRayLCommented:
There is no corresponding opener, nor should there be.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
but this similar code in another database works fine

Sub AppendPerformance(ByVal Email As String, oDate As String, Foreman As String, Shaft As String, MachNum As String, Shift As String, MO As String, AO As String, HN As String, _
DrilledFrom As String, DrilledTo As String, Other As String, DrilledTotal As String, RC As String, RR As String, DH As String, TravHours As String, _
TransHours As String, LDelays As String, MineDelays As String, GroutHours As String, SurveyHours As String, Machine_Maintain As String, DWRHours As String, _
TotalHours As String, Remarks As String)


On Error GoTo EH

'Certain values won't go in so need to make sure you replace them with a space or zero
If DrilledFrom = "" Then DrilledFrom = "0.00"
If DrilledTo = "" Then DrilledTo = "0.00"
If Other = "" Then Other = "0.00"
If DrilledTotal = "" Then DrilledTotal = "0.00"
If RC = "" Then RC = "0.00"
If RR = "" Then RR = "0.00"
If DH = "" Then DH = "0.00"
If TravHours = "" Then TravHours = "0.00"
If TransHours = "" Then TransHours = "0.00"
If LDelays = "" Then LDelays = "0.00"
If MineDelays = "" Then MineDelays = "0.00"
If GroutHours = "" Then GroutHours = "0.00"
If SurveyHours = "" Then SurveyHours = "0.00"
If Machine_Maintain = "" Then Machine_Maintain = "0.00"
If DWRHours = "" Then DWRHours = "0.00"
If TotalHours = "" Then TotalHours = "0.00"


Dim S As String
S = "INSERT INTO Performance (Email, [Date], Supervisor, Foreman, Shaft, Machine_Number, Shift, Managing_Operator, Assistant_Operator, Hole_Number, Drilled_From, Drilled_To, Other, Drilled_Total, Redrilled_C, Redrilled_R, Drilled_Hours, Travel_Hours, Transport_Hours, Lesedi_Delays, Mine_Delays, Grout_Hours, Survey_Hours, Machine_Maintain, DWR_Hours, Total_Hours, Remarks ) " _
& "SELECT " & "'" & Email & "', " & "#" & CDate(oDate) & "# AS oDate, " & "'" & oSupervisor & "', " _
& "'" & Foreman & "', " _
& "'" & Shaft & "', " & "'" & MachNum & "', " _
& "'" & Shift & "', " _
& "'" & MO & "', " _
& "'" & AO & "', " _
& "'" & HN & "', " _
& DrilledFrom & ", " _
& DrilledTo & ", " _
& Other & ", " _
& DrilledTotal & "," _
& RC & ", " _
& RR & ", " _
& DH & ", " _
& TravHours & ", " _
& TransHours & ", " _
& LDelays & ", " _
& MineDelays & ", " _
& GroutHours & ", " _
& SurveyHours & ", " _
& Machine_Maintain & ", " _
& DWRHours & ", " _
& TotalHours & ", " _
& "'" & Remarks & "'"

Debug.Print S

DoCmd.RunSQL S

Exit Sub
EH:
    blnAppend_CellError = True
    sAppendCellError = Err.Description
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
To all that participated in this question. Thank you. The error was an additional comma and a letter missing in a variable. The following code worked:

Sub AppendPerformance(oDate As String, oEnteredBy As String, oForeman As String, oSupervisor As String, oShaft As String, oMachineNumber As String, oHoleNumber As String, oSite As String, _
oShift As String, oManagingOperator As String, oAssistantOperator As String, oAssistantOperator2 As String, oDrilledFrom As String, oDrilledTo As String _
   , oAXT As String, oBX As String, oNX As String, oOther As String, oDrilledTotal As String, oRockRedrill As String, oConcreteRedrill As String, oDrillHours As String, _
   oTravelHours As String, oTransportHours As String, oLesediDelays As String, oMineDelays As String, oGroutHours As String, oSurveyHours As String _
    , oMachineMaintain As String, oDWRHours As String, oTotalHours As String, oRemarks As String)


On Error GoTo EH

'Certain values won't go in so need to make sure you replace them with a space or zero
If oDrilledFrom = "" Then oDrilledFrom = "0.00"
If oDrilledTo = "" Then oDrilledTo = "0.00"
If oAXT = "" Then oAXT = "0.00"
If oBX = "" Then oBX = "0.00"
If oNX = "" Then oNX = "0.00"
If oOther = "" Then oOther = "0.00"
If oDrilledTotal = "" Then oDrilledTotal = "0.00"
If oRockRedrill = "" Then oRockRedrill = "0.00"
If oConcreteRedrill = "" Then oConcreteRedrill = "0.00"
If oDrillHours = "" Then oDrillHours = "0.00"
If oTravHours = "" Then oTravHours = "0.00"
If oTransportHours = "" Then oTransportHours = "0.00"
If oLesediDelays = "" Then oLesediDelays = "0.00"
If oMineDelays = "" Then oMineDelays = "0.00"
If oGroutHours = "" Then oGroutHours = "0.00"
If oSurveyHours = "" Then oSurveyHours = "0.00"
If oMachineMaintain = "" Then oMachineMaintain = "0.00"
If oDWRHours = "" Then oDWRHours = "0.00"
If oTotalHours = "" Then oTotalHours = "0.00"
If oRemarks = "" Then oRemarks = "0.00"


Dim S As String
S = "INSERT INTO Performance ([Date], Entered_By, Foreman, Supervisor, Shaft, Machine_Number, Hole_Number, Site, Shift, Managing_Operator, Assistant_Operator, Assistant_Operator2, Drilled_From, Drilled_To, AXT, BX, NX, Other, Drilled_Total, Rock_Redrill, Concrete_Redrill, Drill_Hours, Travel_Hours, Transport_Hours, Lesedi_Delays, Mine_Delays, Grout_Hours, Survey_Hours, Machine_Maintain, DWR_Hours, Total_Hours, Remarks ) " _
& "SELECT " & "#" & CDate(oDate) & "# AS oDate, " _
& "'" & oEnteredBy & "', " & "'" & oForeman & "', " & "'" & oSupervisor & "', " & "'" & oShaft & "', " & "'" & oMachineNumber & "', " & "'" & oHoleNumber & "', " & "'" & oSite & "', " & "'" & oShift & "', " _
& "'" & oManagingOperator & "'," & "'" & oAssistantOperator & "'," & "'" & oAssistantOperator2 & "'," _
& oDrilledFrom & ", " _
& oDrilledTo & ", " _
& oAXT & ", " _
& oBX & ", " _
& oNX & ", " _
& oOther & ", " _
& oDrilledTotal & "," _
& oRockRedrill & ", " _
& oConcreteRedrill & ", " _
& oDrillHours & ", " _
& oTravelHours & ", " _
& oTransportHours & ", " _
& oLesediDelays & ", " _
& oMineDelays & ", " _
& oGroutHours & ", " _
& oSurveyHours & ", " _
& oMachineMaintain & ", " _
& oDWRHours & ", " _
& oTotalHours & ", " _
& "'" & oRemarks & "'"

Debug.Print S


DoCmd.RunSQL S

Exit Sub
EH:
    blnAppend_CellError = True
    sAppendCellError = Err.Description
    MsgBox Err.Description

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now