Access Dlookup Multiple Criteria Error

[AndrewKent]

Hi there,

I have the following Access query that I have put into Excel:

    If strQueryType = "Download_Headcount_WL5" Then
        strQuery = "SELECT [ENMatrix_Forenames] & ' ' & [ENMatrix_Surname] AS WL5, tblData_ChangeLog.CL_Week AS WEEK, DLookUp('[DateMatrix_Period]','[tblMatrix_DateMatrix]','[tblMatrix_DateMatrix]![DateMatrix_Week] = 19' And '[tblMatrix_DateMatrix]![DateMatrix_Year]='2011-2012'') AS PERIOD, tblData_ChangeLog.CL_Year AS [YEAR], Count(tblData_ChangeLog.CL_EmployeeNumber) AS [TOTAL VOLUME], Sum(tblData_ChangeLog.CL_FTE) AS [TOTAL FTE] " _
        & "FROM tblDefinitions_OverallArea RIGHT JOIN (tblMatrix_EmployeeNumbers RIGHT JOIN (tblDefinitions_CCNumbers RIGHT JOIN tblData_ChangeLog ON tblDefinitions_CCNumbers.CCData_OracleCCNumber = tblData_ChangeLog.CL_OracleCCNumber) ON tblMatrix_EmployeeNumbers.ENMatrix_EmployeeNumber = tblDefinitions_CCNumbers.CCData_WL5Head) ON tblDefinitions_OverallArea.OAD_ExecArea = tblDefinitions_CCNumbers.CCData_ExecArea " _
        & "GROUP BY [ENMatrix_Forenames] & ' ' & [ENMatrix_Surname], tblData_ChangeLog.CL_Week, DLookUp('[DateMatrix_Period]','[tblMatrix_DateMatrix]','[tblMatrix_DateMatrix]![DateMatrix_Week] = 19' And '[tblMatrix_DateMatrix]![DateMatrix_Year]='2011-2012''), tblData_ChangeLog.CL_Year, tblData_ChangeLog.CL_CurrentStatus " _
        & "HAVING (((tblData_ChangeLog.CL_Week) = 19) And ((tblData_ChangeLog.CL_Year) = '2011-2012') And ((tblData_ChangeLog.CL_CurrentStatus) = 'STAFF')) " _
        & "ORDER BY [ENMatrix_Forenames] & ' ' & [ENMatrix_Surname];"
        Exit Sub
    End If

When I run the query in Access it works fine, however when I copy and paste the SQL into Excel I get the error message:

Syntax error (missing operator) in query expression

DLookUp('[DateMatrix_Period]','[tblMatrix_DateMatrix]','[tblMatrix_DateMatrix]![DateMatrix_Week] = 19' And '[tblMatrix_DateMatrix]![DateMatrix_Year]='2011-2012'')

Can anyone help me figure out why this works in Access but not when I copy it into Excel?

Andy

 

[jackd]

I think the quotes are the issue. If you used a combo of " and ', I think it would work.
I'd try

DLookUp("[DateMatrix_Period]","[tblMatrix_DateMatrix]","[tblMatrix_DateMatrix]![DateMatrix_Week] = '19' And [tblMatrix_DateMatrix]![DateMatrix_Year]='2011-2012'")

 

[AndrewKent]

I think the quotes are the issue. If you used a combo of " and ', I think it would work.
I'd try

in VBA you must change all " to '. It doesn't seem to allow the use of " in a SQL?

Andy

 

[jackd]

Is [DateMatrix_Week] = 19 suppose to be numeric or text?

 

[boblarson]

in VBA you must change all " to '. It doesn't seem to allow the use of " in a SQL?

You change all double quotes for criteria but not around the field names nor the table/queryname. It is EXACTLY as jackd has said. That is the correct way to do it in VBA.

 

[AndrewKent]

Ahhhh I'm with you now! Thanks guys!