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

[daveyboy23]

Hi guys,

Hopefully someone can help.

 

[daveyboy23]

Initially I wrote it as Set rs = db.OpenRecordset(qry_CPI_Logged(wc), dbOpenDynaset) but it says that argument is not optional and so I added the productivity assuming it had relevance to either the sheet called productivity, or was a refence to a group of cells within the sheet which have been merged with the name productivity.

 

[daveyboy23]

Function qry_CML_quality(ByVal wc As Date, ByVal table As String) As String
qry_CML_quality = _
"SELECT tblCaseDetails.RespondRef, tblCaseDetails.CHRespondID, " + table + ".Agree " _
+ "FROM (tblCaseDetails INNER JOIN " + table + " ON tblCaseDetails.RespondRef = " + table + ".RespondRef) " _
+ "WHERE (((" + table + ".CCDate) >= #" + CStr(convert_date(wc)) + "# And (" + table + ".CCDate) < #" + CStr(convert_date(wc + 7)) + "#));"
End Function


Function qry_CPI_Logged(ByVal wc As Date, ByVal table As String) As String
qry_CPI_Logged = _
strSQL = " SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date] "
strSQL = strSQL & " FROM tblCPILogged INNER JOIN tblstafflist ON tblCPILogged.[Logged By] = TblStaffList.RESPONDID  "
strSQL = strSQL & " WHERE tblCPILogged.[Logged Date] >= #6/23/2014# And tblCPILogged.[Logged Date] <= #6/27/2014#"
End Function

So the latter is the vba you suggested in order to bring in the SQL query.

However here....

'DG complete
    Set rs = db.OpenRecordset(qry_DG_productivity(wc), dbOpenDynaset)
    Call print_recordset(rs, Sheet4, "BR5", "BR5:BT5005", 2)
    
    'NCR Scan
    'Set rs = db.OpenRecordset(qry_NCRScan_productivity(wc), dbOpenDynaset)
    'Call print_recordset(rs, Sheet4, "BZ5", "BZ5:CB5005", 2)
    
    'NCR CPI Logging
    Set rs = db.OpenRecordset(qry_CPI_Logged_productivity(wc), dbOpenDynaset)
    Call print_recordset(rs, Sheet4, "CT5", "CT5:CV5005", 2)

I was following the same pattern as shown in DG complete, but changing the output cell and the range, what do I need to change, in order to comply with defining the sub function?

 

[daveyboy23]

Okay, I get what you are saying, but how do I include the SQL statement within this?

 

[daveyboy23]

I think given the fact that at this moment I feel completely stuck, I will ask a colleague on Monday and hopefully they can show me the light

 

[Norie]

I'm sorry but you've totally lost me.

 

[daveyboy23]

Okay, so basically this is defining the output of a query I have created from Access 'NCR CPI Logging
Set rs = db.OpenRecordset(qry_CPI_Logged_productivity(wc), dbOpenDynaset)
Call print_recordset(rs, Sheet4, "CT5", "CT5:CV5005", 2)

In turn this

Function qry_CPI_Logged(ByVal wc As Date, ByVal table As String) As String
qry_NCR_CPI_LOgging = _
strSQL = " SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date] "
strSQL = strSQL & " FROM tblCPILogged INNER JOIN tblstafflist ON tblCPILogged.[Logged By] = TblStaffList.RESPONDID "
strSQL = strSQL & " WHERE tblCPILogged.{Logged Date}>=#" + CStr(convert_date(wc)) + "# AND CHLastModDate<#" + CStr(convert_date(wc + 7))
is to provide the SQL function.

The idea is that this updated query can then be pulled through

I have changed your SQL slightly because the query is relevant from the beginning to end of work week, which is recognised by the week commencing week we enter and will then only pull through the relevant week.

Each time I attempt to run the macro, it states that I have not defined the function

 

[Norie]

What I posted wasn't meant to be used like that.

Also, you haven't defined a function named qry_CPI_Logged_productivity.

The function you have defined is called qry_CPI_Logged and takes 2 arguments, wc and table.

 

[daveyboy23]

Right, so I decided to go back to this after my brain had some downtime as it was a busy and stressful week.

qry_NCR_CPI_Logged = _
" SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date] "_
+ "FROM tblCPILogged INNER JOIN tblstafflist ON tblCPILogged.[Logged By] = TblStaffList.RESPONDID "_
+ "WHERE (((tblCPILogged.[Logged Date])>=#" + CStr(convert_date(wc)) + "# And (tblCPILogged.[Logged.Date])<#" + CStr(convert_date(wc + 7)) + "#));"

That's me defining the function

This is the output defined

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


The first error I get is argument not optional and the second one is the defining function, it doesn't like what has been entered.

Again, hopefully someone can help

 

[Norie]

Firstly, this is not defining a function.

qry_NCR_CPI_Logged = _
" SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date] "_
+ "FROM tblCPILogged INNER JOIN tblstafflist ON tblCPILogged.[Logged By] = TblStaffList.RESPONDID "_
+ "WHERE (((tblCPILogged.[Logged Date])>=#" + CStr(convert_date(wc)) + "# And (tblCPILogged.[Logged.Date])<#" + CStr(convert_date(wc + 7)) + "#));"

qry_NCE_CPI_Logged is a string variable that contains your SQL statement.

This is how you would execute it.

Set rs = db.OpenRecordset(qry_CPI_Logged, dbOpenDynaset)

By the way, & is the concatenation operator, not +. If you use +, the addition operator, it can cause problems.

 

[daveyboy23]

Apologies for for my self evident lack of knowledge, but concerning what I described as a defining function, what I have done there, or thought I had done there, was clearly define the SQL.
What you are saying is that I should replace + with &, regarding designating the path, when I enter in
Set rs = db.OpenRecordset(qry_CPI_Logged, dbOpenDynaset)</PRE> it tells me that argument is not optional.