How do I paste in an SQL code into my excel vba code?

[daveyboy23]

Hi guys,

Hopefully someone can help.

 

[Norie]

I don't follow.

Are you getting a syntax error when you try and execute that query in the database?

Or are you getting it somewhere else?

PS There's a missing # after the first date.

 

[daveyboy23]

Effectively I am asking how do I build that above code into my VBA and before that, what is the problem in the syntax?

 

[daveyboy23]

Thanks for that, of course, what an idiot, I am being.

I want to test if this SQL runs, but whilst doing so....it wants me to enter a parameter value for Ref, but my understanding was that I had denoted the table and the .then gave the extension, the extension being ref

Yep, the error is from the database

 

[Norie]

What 'extension'?

The syntax to refer to a field name in SQL is [tablename].[fieldname].

 

[daveyboy23]

Okay, this is my SQL, I just need to put this in terms which vba understands

SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date]
FROM TblStaffList INNER JOIN TblCPILogged ON TblStaffList.RESPONDID = TblCPILogged.[Logged By]
WHERE TblCPILogged.[Logged Date] >=#6/23/2014# AND TblCPILogged.[Logged Date] <=#6/27/2014#

 

[Norie]

Well I'd construct that SQL statement in VBA like this.

strSQL = " SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date] "
strSQL = strSQL & " FROM TblStaffList INNER JOIN TblCPILogged ON TblStaffList.RESPONDID = TblCPILogged.[Logged By] "
strSQL = strSQL & " WHERE tblCPILogged.[Logged Date] >= #6/23/2014# And tblCPILogged.[Logged Date] <= #6/27/2014#"

 

[daveyboy23]

Will that fit in with the VBA written above that though?

 

[daveyboy23]

Okay, so I did what you sugggested, I now have this error appearing

'NCR CPI Logging
Set rs = db.OpenRecordset(qry_CPI_Logged_productivity(wc), dbOpenDynaset)
Call print_recordset(rs, Sheet4, "CT5", "CT5:CV5005", 2)

It says sub or function not defined

 

[Norie]

Do you have a sub/function called print_recordset?

An array/sub/function named qry_CPI_Logged_productivity?

Where are you referring to the SQL statement?

 

[daveyboy23]

Sub print_recordset(ByVal rs As Recordset, ByRef sht As Worksheet, ByVal start_cell As String, ByVal range_cell As String, ByVal fld As Integer)

I believe this is what you are referring to?