Troubles with variables in a SQL-string

[marjdk]

Hi all,

I have this piece of vba-code which doesn't work for me.

Sub HentNavisionData()
Dim KtnNr As String, StartDato As Date, SlutDato As Date
KtnNr = "'     " & Range("B5").Value & "'"
StartDato = Range("B6").Value
SlutDato = Range("B7").Value
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=xaldb;Description=xaldb;UID=xaldb;;;;DATABASE=xaldb", Destination:=Range("G19"))
        .CommandText = Array("SELECT LEDTRANS.TXT, LEDTRANS.DATE_, LEDTRANS.AMOUNTMST" & Chr(13) & "" & Chr(10) & "FROM xaldb.dbo.LEDTRANS LEDTRANS" & Chr(13) & "" & Chr(10) & "WHERE (LEDTRANS.DATE_>={ts '" & StartDato & "'} And LEDTRANS.DATE_<={ts '" & SlutDato & "'}) AND (LEDTRANS.ACCOUNTNUMBER=" & KtnNr & ")" & Chr(13) & "" & Chr(10) & "ORDER BY LEDTRANS.DATE_ DESC")
        .Name = "Navision data"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
 
 
End Sub

When I try to execute this piece of code it returns a error: "Runtime error '1004' General ODBC error". The code works fine without variables in the SQL-string.

Could you please help me solving this issue, thanks.

 

[ByteMyzer]

What is the syntax without the variables?

 

[marjdk]

Like this

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=xaldb;Description=xaldb;;;APP=Microsoft Office 2003;;DATABASE=xaldb" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT LEDTRANS.ACCOUNTNUMBER, LEDTRANS.DATE_, LEDTRANS.TXT, LEDTRANS.AMOUNTMST" & Chr(13) & "" & Chr(10) & "FROM xaldb.dbo.LEDTRANS LEDTRANS" & Chr(13) & "" & Chr(10) & "WHERE (LEDTRANS.ACCOUNTNUMBER='     90024') AND (LEDTRANS.DATE_>={ts '2009-04-01" _
        , _
        " 00:00:00'} And LEDTRANS.DATE_<={ts '2009-04-30 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY LEDTRANS.DATE_" _
        )
        .Name = "Query from xaldb"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

 

[ByteMyzer]

Try:

Sub HentNavisionData()
 
Dim KtnNr As String, StartDato As Date, SlutDato As Date
KtnNr = "'     " & Range("B5").Value & "'"
StartDato = Range("B6").Value
SlutDato = Range("B7").Value
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=xaldb;Description=xaldb;UID=xaldb;;;;DATABASE=xaldb", Destination:=Range("G19"))
        .CommandText = "SELECT LEDTRANS.TXT, LEDTRANS.DATE_, LEDTRANS.AMOUNTMST" _
            & " FROM xaldb.dbo.LEDTRANS LEDTRANS" _
            & " WHERE (LEDTRANS.DATE_>={ts '" & [B][COLOR=red]Format([/COLOR][/B]StartDato[B][COLOR=red], "yyyy-mm-dd hh:mm:ss")[/COLOR][/B] & "'}" _
            & " AND LEDTRANS.DATE_<={ts '" & [B][COLOR=red]Format([/COLOR][/B]SlutDato[B][COLOR=red], "yyyy-mm-dd hh:mm:ss")[/COLOR][/B] & "'})" _
            & " AND (LEDTRANS.ACCOUNTNUMBER=" & KtnNr & ")" _
            & " ORDER BY LEDTRANS.DATE_ DESC"
        .Name = "Navision data"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
 
End Sub

 

[marjdk]

There seems to be a syntax error :-(

 

[MSAccessRookie]

There seems to be a syntax error :-(

I think that the Format() Function examples have a Type-o. They appear to be missing a closing Quote.

Format(StartDato, "yyyy-mm-dd hh:mm:ss) needs to be Format(StartDato, "yyyy-mm-dd hh:mm:ss").



Two other observations:

1. In VB, the phrase "Chr(13) & "" & Chr(10) " can be replaced with a built in Constant called VBCrLf.
2. Consider using the Between statement to verify the Date Range.

"(LEDTRANS.DATE_ BETWEEN {ts '" & StartDato & "'} And {ts '" & SlutDato & "'})"

Instead of

"(LEDTRANS.DATE_ >= {ts '" & StartDato & "'} And LEDTRANS.DATE_ <= {ts '" & SlutDato & "'})"
​

 

[ByteMyzer]

@MSAccessRookie,

Yep, you're right, my too-fast-fingers are guilty again. I left off the closing quotes.

 

[MSAccessRookie]

@MSAccessRookie,

Yep, you're right, my too-fast-fingers are guilty again. I left off the closing quotes.

I agree. I was actually reviewing the "syntax without the variables" when I spotted the missing quote.