Solved

Access SQL Syntax error

Posted on 2011-03-25
11
256 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
Comment Utility
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
Comment Utility
FROM????
You're select statement doesn't have a FROM part.
OM Gang
0
 

Author Comment

by:murbro
Comment Utility
You don't need a FROM in an Insert SQL statement
0
 
LVL 40

Expert Comment

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

Author Comment

by:murbro
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 28

Expert Comment

by:omgang
Comment Utility
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
Comment Utility
In addition, the closing parenthesis following Remarks in the INSERT INTO section.  
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
There is no corresponding opener, nor should there be.
0
 

Author Comment

by:murbro
Comment Utility
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
Comment Utility
thanks very much
0
 

Author Comment

by:murbro
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

11 Experts available now in Live!

Get 1:1 Help Now