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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
You'll need to let us know what you're trying to do, as the sql will need to be changed into the syntax understood by VBA.
 
Upvote 0
Well, it seems I have lost my previous query

This is the SQL which now contains a syntax error :(

SELECT tblCPILogged.Ref, tblCPILogged.[Logged By], tblCPILogged.[Logged Date]

FROM tblCPILogged

WHERE [TblCPILogged.[Logged Date] >=#6/23/2014# AND TblCPILogged.[Logged.Date] <=#6/27/2014]

As per the other functions....such as this one Function qry_NCRScan_productivity(ByVal wc As Date) As String
qry_NCRScan_productivity = _
"SELECT Ref, CreatedBy, CreatedDate " & _
"FROM tblCreated " & _
"WHERE TaskType='[AUTO] View New Correspondence' " & _
"AND ((tblCreated.CreatedDate)>=#" + CStr(convert_date(wc)) + "# And (tblCreated.CreatedDate)<#" + CStr(convert_date(wc + 7)) + "#);"
End Function


I am trying to update excel to pull through the new query
 
Upvote 0
Is this an SQL statement you are trying to construct in code?

If it is can you post the actual code.
 
Upvote 0
I think the point I am trying to make is that above I posted the SQL I created which is now incorrect and as per the code below, I was trying to create it in the same mould as the one I showed below the SQL. After I typed as per the other functions was an example of an existing function which is already present in the vba
 
Upvote 0
It would still help to see the VBA you are using to create the SQL.

I can actually spot a typo - the [ before here TblCPILogged here

WHERE [TblCPILogged
 
Upvote 0
Thank you for that, I have actually just changed the SQL slightly, why is this producing a syntax error?

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#
 
Upvote 0
This is the existing vba code
Code: 
Function convert_date(ByVal date_val As Date) As Date
convert_date = CDate(CStr(CStr(Month(date_val)) + "/" + CStr(day(date_val)) + "/" + CStr(Year(date_val))))
End Function
' DB Productivity queries------------------------------------------------------------------------------------
Function qry_OFS_productivity(ByVal wc As Date, ByVal prod As String, ByVal stage As String) As String
qry_OFS_productivity = _
"SELECT tblQACheckDetails.RespondRef, tblQACheckDetails.QARespondID, tblQACheckDetails.DateOfQA " _
+ "FROM tblQACheckDetails INNER JOIN tblQACaseDetails ON ((tblQACheckDetails.Stage = tblQACaseDetails.Stage) AND (tblQACheckDetails.RespondRef = tblQACaseDetails.RespondRef)) " _
+ "WHERE (((tblQACheckDetails.DateOfQA)>=#" + CStr(convert_date(wc)) + "# And (tblQACheckDetails.DateOfQA)<#" + CStr(convert_date(wc + 7)) + "#)) " _
+ "AND ((tblQACheckDetails.CheckNumber)=1) " _
+ prod + stage + ";"
End Function
Function qry_CML_productivity(ByVal wc As Date, ByVal prod As String) As String
qry_CML_productivity = _
"SELECT tblCCInitialCheck.RespondRef, tblCCInitialCheck.CCRespondID, tblCCInitialCheck.CCDate " _
+ "FROM tblCCInitialCheck INNER JOIN tblCaseDetails ON tblCCInitialCheck.RespondRef=tblCaseDetails.RespondRef " _
+ "WHERE (((tblCCInitialCheck.CCDate)>=#" + CStr(convert_date(wc)) + "# And (tblCCInitialCheck.CCDate)<#" + CStr(convert_date(wc + 7)) + "#)) " _
+ prod + ";"
End Function
Function qry_CML_re_checks(ByVal wc As Date) As String
qry_CML_re_checks = _
"SELECT tblCCReCheck.RespondRef, tblCCReCheck.CCRespondID, tblCCReCheck.CCDate " _
+ "FROM tblCCReCheck " _
+ "WHERE (((tblCCReCheck.CCDate)>=#" + CStr(convert_date(wc)) + "# And (tblCCReCheck.CCDate)<#" + CStr(convert_date(wc + 7)) + "#));"
End Function
Function qry_set_ch_productivity(ByVal wc As Date) As String
qry_set_ch_productivity = _
"SELECT tblDone.Ref, tblDone.AllocatedTo, tblDone.DoneDate " _
+ "FROM tblDone " _
+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType) Like '*Perform Acceptance')) " _
+ "ORDER BY tblDone.Ref;"

'productivity from SAS
'"SELECT tblSetCaseDetails.RespondRef, tblSetCaseDetails.CHRespondID, tblSetCaseDetails.DateWorked " _
'+ "FROM tblSetCaseDetails " _
'+ "WHERE (((tblSetCaseDetails.DateWorked)>=#" + CStr(convert_date(wc)) + "# And (tblSetCaseDetails.DateWorked)<#" + CStr(convert_date(wc + 7)) + "#));"
End Function

Function qry_TEL_QA_productivity(ByVal wc As Date) As String
qry_TEL_QA_productivity = _
"SELECT tblTelQAChecks.CaseRef, tblTelQAChecks.QAName, tblTelQAChecks.QADate " _
+ "FROM tblStaffList INNER JOIN tblTelQAChecks ON tblStaffList.RespondID = tblTelQAChecks.QAName " _
+ "WHERE (((tblTelQAChecks.QADate)>=#" + CStr(convert_date(wc)) + "# And (tblTelQAChecks.QADate)<#" + CStr(convert_date(wc + 7)) + "#)) AND ((tblTelQAChecks.Stage)='FLQA');"
End Function
Function qry_ack_productivity(ByVal wc As Date) As String
qry_ack_productivity = _
"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone LEFT JOIN qry_Acknowledgement_Tasks_Done ON tblDone.Ref = qry_Acknowledgement_Tasks_Done.Ref " _
+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType)='[AUTO] Complaint Acknowledgement' Or (tblDone.TaskType)='[AUTO] PBR Acknowledgement' Or (tblDone.TaskType)='[AUTO] PPI Acknowledgement')) OR (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType)='PPI NCR Reject') AND ((qry_Acknowledgement_Tasks_Done.Ref) Is Null)) " _
+ "UNION ALL " _
+ "SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone INNER JOIN tblStaffList ON tblDone.DoneBy = tblStaffList.RespondID " _
+ "WHERE (((tblDone.TaskType) Like '*complex query ack*') AND ((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblStaffList.SubRole) Not Like '*tel*')) "

End Function
Function qry_offer_productivity(ByVal wc As Date) As String
qry_offer_productivity = _
"SELECT tblDone.Ref, tblDone.AllocatedTo, tblDone.DoneDate " _
+ "FROM (tblDone INNER JOIN qryData ON (tblDone.Ref = qryData.Ref) AND (tblDone.DoneDate = qryData.MaxOfDoneDate)) " _
+ "LEFT JOIN qryPT_Acknowledged ON tblDone.Ref = qryPT_Acknowledged.Ref " _
+ "WHERE (((tblDone.DoneDate) >= #" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate) < #" + CStr(convert_date(wc + 7)) + "#) And ((tblDone.TaskType) Like '*Final Letter') And ((IIf([tblDone].[AllocatedTo] = [qryPT_Acknowledged].[DoneBy], 'y', 'n')) = 'n' Or (IIf([tblDone].[AllocatedTo] = [qryPT_Acknowledged].[DoneBy], 'y', 'n')) Is Null)) " _
+ "ORDER BY tblDone.Ref;"
'Previous query
'"SELECT tblDone.Ref, Min(tblDone.AllocatedTo) AS MinOfAllocatedTo, Min(Int([tblDone.DoneDate])) AS [Date] " _
'+ "FROM tblDone LEFT JOIN qryPT_Acknowledged ON tblDone.Ref = qryPT_Acknowledged.Ref " _
'+ "WHERE (((tblDone.TaskType)='[AUTO] Final Letter') AND ((IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n'))='n' Or (IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n')) Is Null)) OR (((tblDone.TaskType)='[AUTO] PBR Final Letter') AND ((IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n'))='n' Or (IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n')) Is Null)) " _
'+ "GROUP BY tblDone.Ref " _
'+ "HAVING (((Min(Int([tblDone.DoneDate])))>=#" + CStr(convert_date(wc)) + "# And (Min(Int([tblDone.DoneDate])))<#" + CStr(convert_date(wc + 7)) + "#)) OR (((Min(Int([tblDone.DoneDate])))>=#" + CStr(convert_date(wc)) + "# And (Min(Int([tblDone.DoneDate])))<#" + CStr(convert_date(wc + 7)) + "#)); "
 
End Function
Function qry_set_qa_productivity(ByVal wc As Date, ByVal check As String) As String
qry_set_qa_productivity = _
"SELECT tblSetStageCheckA.RespondRef, tblSetStageCheckA.CheckerRespondID, tblSetStageCheckA.CheckDate " _
+ "FROM tblSetStageCheckA INNER JOIN tblSetInitialCheck ON (tblSetStageCheckA.RespondRef=tblSetInitialCheck.RespondRef AND tblSetStageCheckA.CaseInstance=tblSetInitialCheck.CaseInstance) " _
+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblSetStageCheckA.CheckNumber)=1)) " _
+ check + ";"
End Function
Function qry_SetReKey_productivity(ByVal wc As Date) As String
qry_SetReKey_productivity = _
"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone " _
+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType)='Z - PPI Request Re-Key'));"
End Function
Function qry_DG_productivity(ByVal wc As Date) As String
qry_DG_productivity = _
"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone " _
+ "WHERE tblDone.Ref Not Like 'SLC*' AND (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND " _
+ "((tblDone.TaskType)='PPI Request Micrographics (New)' Or (tblDone.TaskType)='PPI DG Complete - Assessment Required' Or (tblDone.TaskType)='PPI DG Reject'));"

'Doesn't exclude SLC cases
'qry_DG_productivity = _
'"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
'+ "FROM tblDone " _
'+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND " _
'+ "((tblDone.TaskType)='PPI Request Micrographics (New)' Or (tblDone.TaskType)='PPI DG Complete - Assessment Required' Or (tblDone.TaskType)='PPI DG Reject'));"
End Function
Function qry_NCRBP_productivity(ByVal wc As Date) As String
qry_NCRBP_productivity = _
"SELECT tblCreated.Ref, tblCreated.CreatedBy, tblDone.DoneDate " _
+ "FROM tblCreated INNER JOIN tblDone ON (tblCreated.TaskNote = tblDone.TaskNote) AND (tblCreated.TaskType = tblDone.TaskType) AND (tblCreated.Ref = tblDone.Ref) " _
+ "WHERE ((tblCreated.CreatedDate)>=#" + CStr(convert_date(wc)) + "# And (tblCreated.CreatedDate)<#" + CStr(convert_date(wc + 7)) + "#) AND (((tblDone.TaskType)='Ad Hoc') AND (tblDone.TaskNote Like 'Blue Prism Request*' OR tblDone.TaskNote Like 'BP Request*') AND ((IIf([CreatedBy]=[DoneBy],'Y','N'))='N'));"
End Function
Function qry_NCRScan_productivity(ByVal wc As Date) As String
qry_NCRScan_productivity = _
"SELECT Ref, CreatedBy, CreatedDate " & _
"FROM tblCreated " & _
"WHERE TaskType='[AUTO] View New Correspondence' " & _
"AND ((tblCreated.CreatedDate)>=#" + CStr(convert_date(wc)) + "# And (tblCreated.CreatedDate)<#" + CStr(convert_date(wc + 7)) + "#);"
End Function
Function qry_SARS_productivity(ByVal wc As Date) As String
qry_SARS_productivity = _
"SELECT SARSKey, CHRespondID, CHLastModDate " & _
"FROM tblSARSData INNER JOIN tblStatuses ON tblSARSData.CHStatus=tblStatuses.Status " & _
"WHERE CHLastModDate>=#" + CStr(convert_date(wc)) + "# AND CHLastModDate<#" + CStr(convert_date(wc + 7)) + "# AND Complete=True;"
End Function

Function qry_SARS_QA_productivity(ByVal wc As Date) As String
qry_SARS_QA_productivity = _
"SELECT SARSKey, QARespondID, QADate " & _
"FROM tblSARSQAChecks " & _
"WHERE QADate>=#" + CStr(convert_date(wc)) + "# AND QADate<#" + CStr(convert_date(wc + 7)) + "#;"
End Function
' DB Productivity queries------------------------------------------------------------------------------------
' DB Quality queries-----------------------------------------------------------------------------------------
Function qry_OFS_quality(ByVal wc As Date, ByVal stage As Integer) As String
qry_OFS_quality = _
"SELECT tblQACaseDetails.RespondRef, tblQACaseDetails.CHRespondID, IIf([tblQACheckDetails]![OutcomeFail]=True,'Outcome',[tblQACheckDetails]![QAGrade]) " _
+ "FROM tblQACaseDetails INNER JOIN tblQACheckDetails ON ((tblQACaseDetails.RespondRef = tblQACheckDetails.RespondRef) AND (tblQACaseDetails.Stage = tblQACheckDetails.Stage)) " _
+ "WHERE (((tblQACheckDetails.DateOfQA)>=#" + CStr(convert_date(wc)) + "# And (tblQACheckDetails.DateOfQA)<#" + CStr(convert_date(wc + 7)) + "#)) " _
+ "AND ((tblQACheckDetails.CheckNumber)=1) AND tblQACheckDetails.Stage=" + CStr(stage) + ";"
End Function
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 = _
"sELECT" Ref, PA Product, Logged Date, Logged By " & _
"FROM
End Function
Function qry_SAS_quality(ByVal wc As Date, ByVal check As String) As String
qry_SAS_quality = _
"SELECT tblSetCaseDetails.RespondRef, tblSetStageCheckA.CHRespondID, iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) " _
+ "FROM (tblSetCaseDetails INNER JOIN tblSetInitialCheck ON (tblSetCaseDetails.RespondRef = tblSetInitialCheck.RespondRef) AND (tblSetCaseDetails.CaseInstance = tblSetInitialCheck.CaseInstance)) INNER JOIN tblSetStageCheckA ON (tblSetInitialCheck.RespondRef = tblSetStageCheckA.RespondRef) AND (tblSetInitialCheck.CaseInstance = tblSetStageCheckA.CaseInstance) " _
+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) " _
+ "AND ((tblSetInitialCheck.CheckType)" + check + ") AND ((tblSetStageCheckA.CheckNumber)=1)) " _
+ "ORDER BY iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) ASC;"
'old version with CH Respond ID coming from SetCaseDetails table
'qry_SAS_quality = _
'"SELECT tblSetCaseDetails.RespondRef, tblSetCaseDetails.CHRespondID, iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) " _
'+ "FROM (tblSetCaseDetails INNER JOIN tblSetInitialCheck ON (tblSetCaseDetails.RespondRef = tblSetInitialCheck.RespondRef) AND (tblSetCaseDetails.CaseInstance = tblSetInitialCheck.CaseInstance)) INNER JOIN tblSetStageCheckA ON (tblSetInitialCheck.RespondRef = tblSetStageCheckA.RespondRef) AND (tblSetInitialCheck.CaseInstance = tblSetStageCheckA.CaseInstance) " _
'+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) " _
'+ "AND ((tblSetInitialCheck.CheckType)" + check + ") AND ((tblSetStageCheckA.CheckNumber)=1)) " _
'+ "ORDER BY iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) ASC;"
'"SELECT tblSetCaseDetails.RespondRef, First(tblSetCaseDetails.CHRespondID), First(iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome)) " _
'+ "FROM (tblSetCaseDetails INNER JOIN tblSetInitialCheck ON (tblSetCaseDetails.RespondRef = tblSetInitialCheck.RespondRef) AND (tblSetCaseDetails.CaseInstance = tblSetInitialCheck.CaseInstance)) INNER JOIN tblSetStageCheckA ON (tblSetInitialCheck.RespondRef = tblSetStageCheckA.RespondRef) AND (tblSetInitialCheck.CaseInstance = tblSetStageCheckA.CaseInstance) " _
'+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) " _
'+ "AND ((tblSetInitialCheck.CheckType)" + check + ") AND ((tblSetStageCheckA.CheckNumber)=1)) " _
'+ "GROUP BY tblSetCaseDetails.RespondRef " _
'+ "ORDER BY First(iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome)) ASC;"
End Function
Function qry_cc_flqa(ByVal wc As Date) As String
qry_cc_flqa = _
"SELECT tblCCInitialCheck.RespondRef, tblCCInitialCheck.CCRespondID, " _
+ "IIF([tblQACheckDetails]![CCPWL]=true,'PWL',IIF([tblQACheckDetails]![CCCalcFail]=true,'Outcome',IIF([tblQACheckDetails]![CCEvidenceFail]=true,'Fail','Pass'))) AS CCGrade " _
+ "FROM tblCCInitialCheck INNER JOIN tblQACheckDetails ON tblCCInitialCheck.RespondRef = tblQACheckDetails.RespondRef " _
+ "WHERE (((tblQACheckDetails.DateOfQA)>=#" + CStr(convert_date(wc)) + "# And (tblQACheckDetails.DateOfQA)<#" + CStr(convert_date(wc + 7)) + "#)) AND ((tblQACheckDetails.CheckNumber)=1) AND tblQACheckDetails.Stage=3;"
End Function
Function qry_TEL_CH_quality(ByVal wc As Date) As String
qry_TEL_CH_quality = _
"SELECT tblTelQAChecks.CaseRef, tblTelQAChecks.CHName, IIf([QAOutcome]='Fail (QC)','Fail',IIf([QAOutcome]='Fail (Out)','Outcome',[QAOutcome])) AS Grade " _
+ "FROM tblStaffList AS tblStaffList_1 INNER JOIN (tblStaffList INNER JOIN tblTelQAChecks ON tblStaffList.RespondID = tblTelQAChecks.CHName) ON tblStaffList_1.RespondID = tblTelQAChecks.QAName " _
+ "WHERE (((tblTelQAChecks.QADate)>=#" + CStr(convert_date(wc)) + "# And (tblTelQAChecks.QADate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblTelQAChecks.Stage)='FLQA'));"
End Function
Function qry_SARS_quality(ByVal wc As Date) As String
qry_SARS_quality = _
"SELECT tblSARSQAChecks.SARSKey, tblSARSData.CHRespondID, tblSARSQAChecks.QAOutcome " & _
"FROM tblSARSQAChecks " & _
"INNER JOIN tblSARSData ON tblSARSQAChecks.SARSKey=tblSARSData.SARSKey " & _
"WHERE tblSARSQAChecks.QADate>=#" + CStr(convert_date(wc)) + "# " & _
"AND tblSARSQAChecks.QADate<#" + CStr(convert_date(wc + 7)) + "# " & _
"AND tblSARSQAChecks.Check=1;"
End Function
' DB Quality queries-----------------------------------------------------------------------------------------
Function nlookup(ByRef sht As Worksheet, ByVal val As String, ByVal c_val As Integer, ByVal c_ret As Integer) As Single
On Error GoTo errCap
Dim r As Integer: r = 1
With sht
    Do Until .Cells(r, c_val).Value = 0
        
        If UCase(CStr(.Cells(r, c_val).Value)) = UCase(val) Then
            nlookup = .Cells(r, c_val + c_ret - 1).Value
            Exit Function
        End If
        
        r = r + 1
    Loop
End With
errCap:
Exit Function
MsgBox (Err.Description)
MsgBox ("Error occured in Sheet: " + CStr(sht.Name) + " at Row: " + CStr(r) + " and Column: " + CStr(c_val + c_ret - 1))
End Function
Public Function SumNLookups(ByRef sht As Worksheet, ByVal val As String, ByVal numberofweeks As Integer, ReturnColumn) As Single
    Dim i As Integer
    
    For i = 1 To numberofweeks
        SumNLookups = SumNLookups + nlookup(sht, val, ((i - 1) * 9) + 1, ReturnColumn)
    Next i
End Function
 
Last edited by a moderator:
Upvote 0
The only syntax error in that code was with the function Function qry_CPI_Logged.
Code: 
Function qry_CPI_Logged(ByVal wc As Date, ByVal table As String) As String
qry_CPI_Logged = _
"sELECT" Ref, PA Product, Logged Date, Logged By " & _
"FROM
End Function

There should be no " after 'sELECT' and there should be a " after 'FROM' to fix the syntax error.

Mind you the SQL in that function isn't complete.
 
Upvote 0
Sorry, just for the purpose of clarity, I have rewritten the SQL, but it's not liking what I have written, where is the syntax error here?

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#
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Has anyone used OpenLinks or ActualTech drivers?
Replies
0
Views
81
chuckbo
C
N
  • Question
Double Click Macro that creates new sheet using SQL query
Replies
1
Views
118
Fluff
Fluff
M
  • Question
question about SQL in EXCEL
Replies
3
Views
68
JEC
JEC
N
  • Question
Paste values without formatting
Replies
1
Views
135
Joe4
Joe4
C
  • Question
What libraries can be used to access SQL Server databases using Excel/VBA on a Mac?
Replies
0
Views
206
chuckbo
C

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top