Solved

Access SQL Syntax error

Posted on 2011-03-25
11
257 Views
Last Modified: 2012-05-11
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
Comment
Question by:murbro
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 500 total points
ID: 35218062
do a debug.print S and paste the resulting SQL into a query design view. Should be able to find it that way.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35218088
FROM????
You're select statement doesn't have a FROM part.
OM Gang
0
 

Author Comment

by:murbro
ID: 35218194
You don't need a FROM in an Insert SQL statement
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 40

Expert Comment

by:Sharath
ID: 35218221
Then, from where are you getting the data for oDate, oEnteredBy  columns in the SELECT clause?
0
 

Author Comment

by:murbro
ID: 35218235
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
 
LVL 28

Expert Comment

by:omgang
ID: 35218243
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
 
LVL 44

Expert Comment

by:GRayL
ID: 35218246
In addition, the closing parenthesis following Remarks in the INSERT INTO section.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35218263
There is no corresponding opener, nor should there be.
0
 

Author Comment

by:murbro
ID: 35218299
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
 

Author Closing Comment

by:murbro
ID: 35218909
thanks very much
0
 

Author Comment

by:murbro
ID: 35219106
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

777 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