Incorrect syntax at my strsql coding

[dennis1989]

Im having problem running the program. Its say i have incorrect syntax at my strsql coding

Private Sub ComboBox1_Change()
    
    Dim rst As ADODB.Recordset
    
    'Initialize Recordset
    Set rst = New ADODB.Recordset
    
    On Error GoTo errhandle
    
        
    strSq1 = "SELECT DISTINCT activitydesc,materialid FROM activity_table " & _
                "WHERE  activityid in " & _
                "(Select distinct activityid from sample where(sampledt BETWEEN StartDt AND EndDT)"
                
        ' Get Start Date & End Date
                
        StartDt = CDate(Format(Range("A4").Value, "dd-MM-yyyy") & " 00:00:00")
        EndDt = CDate(Format(Range("A4").Value, "dd-MM-yyyy") & " 23:59:59")
            
        ' Re-Connect to Database
        MakeConnection
        If Module1.connectSQLSrv(strRegLogin, strRegPwd, strRegDB, strRegServer) Then
            ' Cursor and Lock Characteristic
            ' adOpenDynamic = high Concurrency & Scrollable
            ' adLockOptimistic = other users able to access data
            ' adCmdText = Command Type (Text)
            
            'Open Recordset
            rst.Open strSql, sqlConnection, adOpenDynamic, adLockOptimistic, adCmdText
            

            With rst
            If .RecordCount = 0 Then
            ComboBox1.Clear
            ComboBox1.AddItem rst![Request]
        Do While Not .EOF
            If Not IsNull(.Fields(0).Value) Then
            ComboBox1.AddItem (.Fields(0).Value)
            End If
            .MoveNext
        Loop
        Else
    Exit Sub
    End If
    End With
    
    Set rst = Nothing
    
    Else
    
            'Frm Login activate = asking for user login
            'MsgBox "Login Failed, please retry!", vbExclamation
            bolConnected = False
            Module1.MakeConnection
        End If
           
    Exit Sub

errhandle:
    MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error!"
    bolConnected = False<<< Having error on this line
    If Not sqlConnection Is Nothing Then
        Set sqlConnection = Nothing
    End If
End Sub

[debasisdas]

May i know why you have posted the code here ?

[ThatThatGuy]

now what do i understand by that code.... plz specify the problem youre facing

[debasisdas]

You need to frame the query string dynamically at run time by concatenating the variables. You need to understand when to use quotes "" and when not . Your variables are within quotes try to fix that. Assign value to the variables first and then concatenate to frame the string.

[dennis1989]

But the error indicated syntax error near the 'in'

strSql = "SELECT DISTINCT activitydesc,ma terialid FROM Activity" & _
"WHERE activityid in " & _
"(SELECT DISTINCT activityid FROM sample where(sampledt BETWEEN " & startdt & " AND " & enddt & "))"

this is my new coding

[vb5prgrmr]

Just jumping in here but there is no space between the word Activity and where in your last post (ActivityWhere) which would be an error near the "in" clause...



Good Luck

[dennis1989]

sorry can explain again cause im really very new in programming

[vb5prgrmr]

debug.print strSQL...(look at your string concatenation)



Good Luck

[dennis1989]

you mean the coding in my strSQL is wrong ? if wrong which part i need to edit ???

[vb5prgrmr]

Two posts up look between the parens...

[dennis1989]

what is string concatenation ? U mind helping me edit the coding? Thanks in advance

[ThatThatGuy]

what is string concatenation ? U mind helping me edit the coding? Thanks in advance

When you join two strings to form a new one is called string concatenation

[MikeTheBike]

Hi

Perhaps this is the problem

"(SELECT DISTINCT activityid FROM sample where sampledt BETWEEN #" & startdt & "# AND #" & enddt & "#)"

You need to delimit the dates with the # sign.

Also you need to cosider the date format, if you computer local date setting is not in American format (mm/dd/yy) then you will need to format the date to American the format to return the correct records.


Just a thought !?

MTB

[dennis1989]

thanks im trying it out now

[dennis1989]

By the way what does it means to delimit the date ? I have checked that my local setting of the date is correct