Skip to main content

Return to Question

Retitled. Reduced unnecessary text.
Source Link
edit approved Feb 19, 2021 at 11:56
Community Bot
edit approved Feb 19, 2021 at 11:56
Community Bot
  • 1
  • 1

VBA / SQL - Pass How to pass VBA string into SQL Server ADODB query - not working?

VBA string to SQL works fine for 'WHERE from other non-date column'

I've tried several attempts: CAST in query, converting both to integers, etc but haven't worked.

Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works fine.

'datecol is type Date in SQL

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strdate, sqldate as String

strdate = "11/10/20"
sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

Set rs = conn.Execute(sqldate)
arrayRows = rs.GetRows
rs.Close
arrayRowsTranspose = Application.Transpose(arrayRows)

    'datecol is type Date in SQL

    Dim conn as ADODB.Connection
    Dim rs as ADODB.Recordset
    Dim strdate, sqldate as String

    strdate = "11/10/20"
    sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

    Set rs = conn.Execute(sqldate)
    arrayRows = rs.GetRows
    rs.Close
    arrayRowsTranspose = Application.Transpose(arrayRows)

VBA / SQL - Pass VBA string into SQL Server ADODB query - not working

VBA string to SQL works fine for 'WHERE from other non-date column'

I've tried several attempts: CAST in query, converting both to integers, etc but haven't worked.

Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works fine.

'datecol is type Date in SQL

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strdate, sqldate as String

strdate = "11/10/20"
sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

Set rs = conn.Execute(sqldate)
arrayRows = rs.GetRows
rs.Close
arrayRowsTranspose = Application.Transpose(arrayRows)

How to pass VBA string into SQL Server ADODB query?

VBA string to SQL works for 'WHERE from other non-date column'

I've tried several attempts: CAST in query, converting both to integers, etc.

Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works.

    'datecol is type Date in SQL

    Dim conn as ADODB.Connection
    Dim rs as ADODB.Recordset
    Dim strdate, sqldate as String

    strdate = "11/10/20"
    sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

    Set rs = conn.Execute(sqldate)
    arrayRows = rs.GetRows
    rs.Close
    arrayRowsTranspose = Application.Transpose(arrayRows)
added 4 characters in body; edited tags; edited title
Source Link
marc_s
marc_s
  • 759.6k
  • 185
  • 1.4k
  • 1.5k

VBA / SQL - Pass VBA string into MS SQL Server ADODB query - not working

VBA string to SQL works fine for 'WHERE from other non-date column' I've

I've tried several attempts: CAST in query, converting both to integers, etc but haven't worked.
Query

Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works fine.

'datecol is type Date in SQL

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strdate, sqldate as String

strdate = "11/10/20"
sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

Set rs = conn.Execute(sqldate)
arrayRows = rs.GetRows
rs.Close
arrayRowsTranspose = Application.Transpose(arrayRows)

VBA / SQL - Pass VBA string into MS SQL ADODB query - not working

VBA string to SQL works fine for 'WHERE from other non-date column' I've tried several attempts: CAST in query, converting both to integers, etc but haven't worked.
Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works fine.

'datecol is type Date in SQL

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strdate, sqldate as String

strdate = "11/10/20"
sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

Set rs = conn.Execute(sqldate)
arrayRows = rs.GetRows
rs.Close
arrayRowsTranspose = Application.Transpose(arrayRows)

VBA / SQL - Pass VBA string into SQL Server ADODB query - not working

VBA string to SQL works fine for 'WHERE from other non-date column'

I've tried several attempts: CAST in query, converting both to integers, etc but haven't worked.

Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works fine.

'datecol is type Date in SQL

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strdate, sqldate as String

strdate = "11/10/20"
sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

Set rs = conn.Execute(sqldate)
arrayRows = rs.GetRows
rs.Close
arrayRowsTranspose = Application.Transpose(arrayRows)
Source Link
itgonerain
itgonerain
  • 39
  • 3

VBA / SQL - Pass VBA string into MS SQL ADODB query - not working

VBA string to SQL works fine for 'WHERE from other non-date column' I've tried several attempts: CAST in query, converting both to integers, etc but haven't worked.
Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works fine.

'datecol is type Date in SQL

Dim conn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strdate, sqldate as String

strdate = "11/10/20"
sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

Set rs = conn.Execute(sqldate)
arrayRows = rs.GetRows
rs.Close
arrayRowsTranspose = Application.Transpose(arrayRows)