Solved

Access SQL Syntax error

Posted on 2011-03-25
11
258 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

860 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