Code: Select all
[Gender] Like '*Female*'
Code: Select all
[Gender] Like '*Female*'
Code: Select all
[Gender] Like '%Female%'
Code: Select all
[Gender] Like '%Male%'
Code: Select all
strSQL = "select [Product Name] from Ptable where [Product Name] in (select [Product Result] from Criteria)"
rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
ws.Range("B5").CopyFromRecordset rst
Code: Select all
strSQL = "TRANSFORM Count([Product Name]) AS total SELECT [Product Name] " & _
"FROM Ptable WHERE [Product Name] IN (" & Mid(strIn, 2) & ") AND MONTH([Sale Date])=" & _
ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value & _
" GROUP BY [Product Name] PIVOT Day([Sale Date]) In " & _
"(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
ws.Range("B5").CopyFromRecordset rst
rst.Close
Code: Select all
strSQL = "TRANSFORM Count([Product Name]) AS total SELECT [Product Name] " & _
"FROM Ptable WHERE [Product Name] IN (select [Product Result] from Criteria) AND MONTH([Sale Date])=" & _
ws.Range("O1").Value & " AND YEAR([Sale Date])=" & ws.Range("N1").Value & _
" GROUP BY [Product Name] PIVOT Day([Sale Date]) In " & _
"(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)"
Code: Select all
... AND Format([Sale Date],'mmmm')='" & _
ws.Range("O1").Value & "' AND ...
Code: Select all
strSQL = "TRANSFORM Count([Gender]) AS total SELECT [Gender] " & _
"FROM tblmytable WHERE [Gender]='Male' AND[Age]='<12 Years' AND Format([CDate],'mmmm')='" & _
ws.Range("AE9").Value & "'AND YEAR([CDate])=" & ws.Range("AI9").Value & _
" GROUP BY [Gender] PIVOT Day([CDate]) In " & _
"(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
ws.Range("C16").CopyFromRecordset rst
rst.Close