Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access SQL Syntax error

Posted on 2011-03-25
11
Medium Priority
?
264 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 2000 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:Murray Brown
ID: 35218194
You don't need a FROM in an Insert SQL statement
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 41

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:Murray Brown
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:Murray Brown
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:Murray Brown
ID: 35218909
thanks very much
0
 

Author Comment

by:Murray Brown
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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